Thread: return setof : alternatives to holder table
Hello List, I have a plpgsql function returning a set of records. The record is effectively a join of some tables. For example, table a (column a1,column a2,column a3,column a4) table b(column b1,column b2,column b4) I am returning a set of (a2,a4,b2). What I do now is to create a empty table foo(column a2,column a4,column b2) then in my function I have record r foo%rowtype I'm not happy with this solution because this foo tables has to be kept around Thanks for any better solution to this mr.wu
On 15/08/10 18:00, zhong ming wu wrote: > Thanks for any better solution to this CREATE TYPE However, you still have to have a special type around just for that function, and you have to *maintain* it to ensure it always matches the types/columns of the input tables. I frequently wish for type inference in PL/PgSQL functions returning query results, so Pg could essentially create and destroy a type along with the function, allowing you to reference columns in the functions results without having to use RETURNS RECORD and all that AS (column-list) pain. Of course, I don't want it badly enough to put my time where my mouth is and try to code it ;-) . I'm not whining about the current situation, just thinking about ways it could improve further. -- Craig Ringer
In response to zhong ming wu : > Hello List, > > I have a plpgsql function returning a set of records. The record is > effectively a join of some tables. > > For example, table a (column a1,column a2,column a3,column a4) > > table b(column b1,column b2,column b4) > > I am returning a set of (a2,a4,b2). What I do now is to create a empty table > > foo(column a2,column a4,column b2) > > then in my function I have > > record r foo%rowtype > > I'm not happy with this solution because this foo tables has to be kept around > > Thanks for any better solution to this You can create a aown typ or you can use IN/OUT-Parameters. I'm prefering IN/OUT-Parameters, see here: http://www.postgresonline.com/journal/archives/129-Use-of-OUT-and-INOUT-Parameters.html http://bytes.com/topic/postgresql/answers/674690-output-parameters-functions Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On Sun, Aug 15, 2010 at 3:10 AM, Craig Ringer <craig@postnewspapers.com.au> wrote:
On 15/08/10 18:00, zhong ming wu wrote:
> Thanks for any better solution to this
CREATE TYPE
However, you still have to have a special type around just for that
function, and you have to *maintain* it to ensure it always matches the
types/columns of the input tables.
I frequently wish for type inference in PL/PgSQL functions returning
query results, so Pg could essentially create and destroy a type along
with the function, allowing you to reference columns in the functions
results without having to use RETURNS RECORD and all that AS
(column-list) pain.
Of course, I don't want it badly enough to put my time where my mouth is
and try to code it ;-) . I'm not whining about the current situation,
just thinking about ways it could improve further.
How about just using OUT parameters?
CREATE FUNCTION FOO(IN _id uuid, OUT col1 text, OUT col2 text)
RETURNS SETOF record AS
BEGIN
select col1, col2 from test where id=_id;
END;
Then your output just has to match the signature of the OUT parameters. And you don't need to define anything when you call it.
Mike
On 15/08/2010 6:18 PM, Mike Christensen wrote: > How about just using OUT parameters? > > CREATE FUNCTION FOO(IN _id uuid, OUT col1 text, OUT col2 text) > RETURNS SETOF record AS > BEGIN > select col1, col2 from test where id=_id; > END; > > Then your output just has to match the signature of the OUT parameters. > And you don't need to define anything when you call it. That works - and in fact is what I often do. For a couple of functions I have a little query that re-generates the OUT param lists based on the contents of the INFORMATION_SCHEMA for those tables and dynamically re-creates the function, too. It'd be kind of nice to have ALTERing a table propagate that sort of change to dependent functions so it didn't have to be manually maintained. Given that it doesn't do that for even views at the momement, though, it'd a pretty minor thing, and after development slows down post-release schema don't tend to change that fast anyway. -- Craig Ringer
On Sun, Aug 15, 2010 at 6:18 AM, Mike Christensen <mike@kitchenpc.com> wrote: > On Sun, Aug 15, 2010 at 3:10 AM, Craig Ringer <craig@postnewspapers.com.au> > wrote: >> >> On 15/08/10 18:00, zhong ming wu wrote: >> >> > Thanks for any better solution to this >> >> CREATE TYPE >> >> However, you still have to have a special type around just for that >> function, and you have to *maintain* it to ensure it always matches the >> types/columns of the input tables. >> >> I frequently wish for type inference in PL/PgSQL functions returning >> query results, so Pg could essentially create and destroy a type along >> with the function, allowing you to reference columns in the functions >> results without having to use RETURNS RECORD and all that AS >> (column-list) pain. >> >> Of course, I don't want it badly enough to put my time where my mouth is >> and try to code it ;-) . I'm not whining about the current situation, >> just thinking about ways it could improve further. >> >> > > How about just using OUT parameters? > CREATE FUNCTION FOO(IN _id uuid, OUT col1 text, OUT col2 text) > RETURNS SETOF record AS > BEGIN > select col1, col2 from test where id=_id; > END; > Then your output just has to match the signature of the OUT parameters. And > you don't need to define anything when you call it. > Mike My function loops through some rows and do "return next" which I think works only with some predefined "type". My first pass on trying to make it work with "OUT" does not work. I don't have that function with me to give a better try though. Here is what I have tried create or replace function te(out a int,out b int) returns setof record as $pgsql$ declare r record; begin r.a := 1; r.b := 2; return next; end; $pgsql$ language plpgsql;
On 08/15/2010 07:57 AM, zhong ming wu wrote:
> Here is what I have tried
>
> create or replace function te(out a int,out b int) returns setof record as
> $pgsql$
> declare
> r record;
> begin
> r.a := 1;
> r.b := 2;
> return next;
> end;
> $pgsql$ language plpgsql;
Try:
create or replace function te(out a int,out b int)
returns setof record as $pgsql$
begin
a := 1;
b := 2;
return next;
end;
$pgsql$ language plpgsql;
contrib_regression=# select * from te();
a | b
---+---
1 | 2
(1 row)
-- or --
create or replace function te()
returns TABLE(a int, b int) as $pgsql$
begin
a := 1;
b := 2;
return next;
end;
$pgsql$ language plpgsql;
contrib_regression=# select * from te();
a | b
---+---
1 | 2
(1 row)
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
Attachment
On Sun, Aug 15, 2010 at 12:21 PM, zhong ming wu <mr.z.m.wu@gmail.com> wrote: > On Sun, Aug 15, 2010 at 11:57 AM, Joe Conway <mail@joeconway.com> wrote: >> On 08/15/2010 07:57 AM, zhong ming wu wrote: >>> Here is what I have tried >>> >>> create or replace function te(out a int,out b int) returns setof record as >>> $pgsql$ >>> declare >>> r record; >>> begin >>> r.a := 1; >>> r.b := 2; >>> return next; >>> end; >>> $pgsql$ language plpgsql; >> >> Try: >> >> create or replace function te(out a int,out b int) >> returns setof record as $pgsql$ >> begin >> a := 1; >> b := 2; >> return next; >> end; >> $pgsql$ language plpgsql; >> >> contrib_regression=# select * from te(); >> a | b >> ---+--- >> 1 | 2 >> (1 row) >> >> -- or -- >> >> create or replace function te() >> returns TABLE(a int, b int) as $pgsql$ >> begin >> a := 1; >> b := 2; >> return next; >> end; >> $pgsql$ language plpgsql; >> >> contrib_regression=# select * from te(); >> a | b >> ---+--- >> 1 | 2 >> (1 row) >> > The second example is perfect in that I just need to insert my table definition into function definition. Thanks.