Thread: JOIN results of refcursor functions
Hi, Is there any way to use INNER, LEFT and RIGHT JOIN between functions returning refcursor type. Explain: function1 as refcursor function2 as refcursor both functions return columns a and b. can i join the results of these functions in such manner (or any other): function3 as refcursor select f1.a, f2.b from function1 as f1 inner join function2 as f2 on f1.a=f2.a; open f3; return f3; Thanks, Milan Oparnica.
Milan Oparnica <milan.opa@gmail.com> writes:
> Is there any way to use INNER, LEFT and RIGHT JOIN between functions
> returning refcursor type.
No. Make them return setof whatever instead.
regards, tom lane
Tom Lane wrote: > Milan Oparnica <milan.opa@gmail.com> writes: >> Is there any way to use INNER, LEFT and RIGHT JOIN between functions >> returning refcursor type. > > No. Make them return setof whatever instead. > > regards, tom lane > I would like yo avoid creating custom composite types required for setof. Is there any function I could use to retrieve the SQL command from named bound cursor from inside another function ? Idea: a. I declare a refcursor function "C1Ref" as select...from <table> b. In the new function (also of refcursor type) where I want to join C1Ref to something I replace the C1Ref call with SQL query text of that cursor (simply as a subquery). For this to work I must have a way to get C1Ref cursors SQL text in the new function. If possible, please give a small example because I'm quite new to PG and I still find very few examples on the web. PG is excellent, but I can't navigate through the documentation well. Thanks, Milan Oparnica
Milan Oparnica escribió: > Tom Lane wrote: >> Milan Oparnica <milan.opa@gmail.com> 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. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Milan Oparnica escribió: >> Tom Lane wrote: >>> Milan Oparnica <milan.opa@gmail.com> 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 ?
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 Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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
>
> 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;
know bug :( - your variable names are in collision with column names.
You have to protect self - use prefixes for variables
postgres=# create table a(a varchar, b int);
CREATE TABLE
postgres=# insert into a values('kuku',10),('juku',20);
INSERT 0 2
postgres=# create function f(out _a varchar, out _b integer) returns
setof record as $$begin return query select * from a; return; end; $$
language plpgsql;
CREATE FUNCTION
postgres=# select * from f(); _a | _b
------+----kuku | 10juku | 20
(2 rows)
regards
Pavel Stehule
>
> 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
>
Milan Oparnica wrote: >> 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 ? :(((>> .... Pavel Stehule wrote: > know bug :( - your variable names are in collision with column names. > You have to protect self - use prefixes for variables > Thanks Pavel, fortunately if you select columns by table reference (table.field) collision is avoided: CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS SETOF record AS $$ BEGIN RETURN QUERY SELECT skladkol.sklid, skladkol.elid FROM skladkol; RETURN; END; $$ LANGUAGE plpgsql; This works fine. Interesting thing is that using OUT parameters performs much faster than using SETOF custom composite type when returning large recordsets. Is this bug coming soon on some to-do-fix-list ? This structure seems to be nice replacement for PERSISTANT PREPARE I was posting some months ago, the only mess is out variables position sensitivity - you must ensure that select statement returns values in exact order as out parameters are declared. Best regards, Milan Oparnica