Re: [SQL] JOIN results of refcursor functions

[Milan Oparnica]

Alvaro Herrera wrote:

Milan Oparnica escribió:

Tom Lane wrote:

Milan Oparnica <[EMAIL PROTECTED]> writes:
Is there any way to use INNER, LEFT and RIGHT JOIN between functions  
returning refcursor type.

No.  Make them return setof whatever instead.


I would like yo avoid creating custom composite types required for setof.


Then use OUT variables.



I've searched documentation (8.3) and didn't find a way to use OUT 
variables in same manner as SETOF (RETURN NEXT doesn't create a record 
type result).


Can you please give an example of how to return select fld1, fld2 from 
table through OUT variables so the caller gets records ? If possible, 
use refcursor as a source ?


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] JOIN results of refcursor functions

[Alvaro Herrera]

Milan Oparnica escribió:

> I've searched documentation (8.3) and didn't find a way to use OUT  
> variables in same manner as SETOF (RETURN NEXT doesn't create a record  
> type result).
>
> Can you please give an example of how to return select fld1, fld2 from  
> table through OUT variables so the caller gets records ?

create function foo (a int, out b int, out c text) returns setof record 
language plpgsql as $$  
begin   

b = 2 * a;  

c = 'dos por a';

return next;

b = 3 * a;  

c = 'tres por a';   

return next;

end; $$ ;   



alvherre=# select * from foo(4);

 b  | c 

+   

  8 | dos por a 

 12 | tres por a

(2 filas)   


I guess you should be able to do the same with cursor operations.  I
haven't seen how you use refcursor in a plpgsql function.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql