Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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? 

(And if not, where might I best suggest this as a possible future enhancement?)

Many thanks for your help,
George

--
George MacKerron  |  +44 (0)20 7193 7369  |  http://personal.lse.ac.uk/mackerro/
Department of Geography & Environment, London School of Economics


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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux