Re: return setof : alternatives to holder table

From: zhong ming wu <mr(dot)z(dot)m(dot)wu(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: return setof : alternatives to holder table
Date: 2010-08-15 14:57:36
Message-ID: AANLkTimckDZYS26Ggfnnb4=sdpi82PZ2cSbh1xS53U9+@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Torsten Zühlsdorff 2010-08-15 15:46:17 Re: InitDB: Bad system call
Previous Message Sandeep Srinivasa 2010-08-15 12:53:37 Relationship between ulimit and effective_cache_size, work_mem, shared_buffers, maintenance_work_mem and vacuum