From: | George MacKerron <g(dot)j(dot)mackerron(at)lse(dot)ac(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Functions returning setof record -- can I use a table type as my return type hint? |
Date: | 2011-08-12 16:04:50 |
Message-ID: | DECA6C84-D8C7-42EE-9926-2645A5E7A9D9@lse.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2011-08-12 16:10:29 | Re: COPY from .csv File and Remove Duplicates |
Previous Message | Rich Shepard | 2011-08-12 15:27:09 | Re: COPY from .csv File and Remove Duplicates |