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
