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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
