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

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