Re: Functions returning setof record -- can I use a table type as my return type hint?

Many thanks for the reply, Ray.

Unfortunately, I don't think this addresses the problem, because I'd hoped not to have to hard-code the table name into the function.

The point of the function is that you can pass it any table name (along with some other parameters) and it returns rows from that named table.

On 12 Aug 2011, at 17:22, Raymond O'Donnell wrote:

> On 12/08/2011 17:04, George MacKerron wrote:
>> Hi all.
>>
>> I have a function returning setof record. The name of a table it acts
>> on is one of its input variables, and its output is a set of rows
>> from that table. E.g. for simplicity, imagine it's this pointless
>> function:
>>
>> create or replace function select_all_from(table_name text) returns
>> setof record as $$ declare begin return query execute 'select * from
>> ' || quote_ident(table_name); end $$ language 'plpgsql' stable;
>>
>> When I now query this I have to do something like the following, with
>> an 'as' clause specifying what is going to be returned:
>>
>> select column_a from select_all_from('some_table') as (column_a
>> integer, column_b text);
>>
>> When some_table has a lot of columns, this is a real pain. I see in
>> the PG documentation that each table is also a type of its own. Thus
>> I had hoped to be able to write the following instead:
>>
>> select id from select_all_from('some_table') as some_table;
>>
>> However, this is rejected ('a column definition list is required for
>> functions returning "record"').
>>
>> So -- is there some other syntax or workaround that I can use to can
>> achieve this -- i.e. to persuade PG to accept a table type in lieu of
>> a manually-recreated column definition list?
>
> What you need to do is declare your function as returning the table type:
>
> create or replace function my_function(....)
> returns my_table
> as.....
>
> And then you can do simply:
>
> select * from my_function(....)
>
> HTH,
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie

Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer