Re: return setof : alternatives to holder table

On Sun, Aug 15, 2010 at 6:18 AM, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> On Sun, Aug 15, 2010 at 3:10 AM, Craig Ringer <craig(at)postnewspapers(dot)com(dot)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;