Обсуждение: 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 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

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.