Re: JOIN results of refcursor functions

Alvaro Herrera wrote:
> 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;
>
I understand this example, but couldn't figure how to do the same thing
with query results.

Please help me build a

function foo(insklid int, out sklid int, out elid int) returns setof record

that will return result of select sklid, elid form skladkol

where skladkol is a table

CREATE TABLE skadkol (sklid int, elid int)

I know this should be simple, but all examples I could find about OUT
parameters use x:=something which is simple but doesn't help.

I've tried:

CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) AS $$
BEGIN
RETURN QUERY SELECT sklid,elid form skladkol where sklid2=insklid;
END;
$$ LANGUAGE plpgsql;

but i get "cannot use RETURN QUERY in a non-SETOF function at or near
"QUERY"

Then I've tried:

CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS
SETOF record AS $$
BEGIN
RETURN QUERY SELECT sklid,elid FROM skladkol;
RETURN;
END;
$$ LANGUAGE plpgsql;

but it returns 5498 rows (which is exact number of rows in that table)
but of NULL values. WHAT AM I DOING WRONG ? :(((

I'm using EMS Manager 2007 Lite for PostgreSQL. That shouldn't be a
problem, right ?

Regards,

Milan Oparnica