Thread: Functions returning setof record -- can I use a table type as my return type hint?
Functions returning setof record -- can I use a table type as my return type hint?
From
George MacKerron
Date:
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
isa 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
ofits 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
tabletype 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
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
Raymond O'Donnell
Date:
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@iol.ie
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
George MacKerron
Date:
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
thefunction.
The point of the function is that you can pass it any table name (along with some other parameters) and it returns rows
fromthat 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@iol.ie
Please access the attached hyperlink for an important electronic communications disclaimer:
http://lse.ac.uk/emailDisclaimer
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
Merlin Moncure
Date:
On Fri, Aug 12, 2011 at 11:26 AM, George MacKerron <g.j.mackerron@lse.ac.uk> wrote: > 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 intothe function. > > The point of the function is that you can pass it any table name (along with some other parameters) and it returns rowsfrom that named table. you can't have it both ways. at the time the function call is executed, the return type/fields must be known. you can do this by either a. explicitly defining the function return type or b. describing the function return type in the function call, or c. use a generic type to hold the output record structure which can be parsed/dealt with later, like text or hstore. merlin
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
Raymond O'Donnell
Date:
On 12/08/2011 17:26, George MacKerron wrote: > 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. OK, fair enough.... but what exactly are you trying to do that a simple SELECT won't do? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
Rob Sargent
Date:
On 08/12/2011 10:04 AM, 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 outputis 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; > > > Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer > returns setof table-name
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
George MacKerron
Date:
On 12 Aug 2011, at 17:43, Merlin Moncure wrote: > you can't have it both ways. at the time the function call is > executed, the return type/fields must be known. you can do this by > either a. explicitly defining the function return type or b. > describing the function return type in the function call, or c. use a > generic type to hold the output record structure which can be > parsed/dealt with later, like text or hstore. Thanks. I'm trying to do your option (a) -- defining the function return type. But I want to do this by referring to an existing table type -- which I know the returned fields must match -- rather thanlaboriously retype the field definition list for that table. The problem is that I can't make the database accept the table type as a field definition list, when that seems like a perfectlysensible (and in this case much more convenient) way to define the fields that will be returned. (With apologies for thoughtless top-posting in reply to Ray's earlier message). Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
Merlin Moncure
Date:
On Fri, Aug 12, 2011 at 12:01 PM, George MacKerron
<g.j.mackerron@lse.ac.uk> wrote:
> On 12 Aug 2011, at 17:43, Merlin Moncure wrote:
>
>> you can't have it both ways. at the time the function call is
>> executed, the return type/fields must be known. you can do this by
>> either a. explicitly defining the function return type or b.
>> describing the function return type in the function call, or c. use a
>> generic type to hold the output record structure which can be
>> parsed/dealt with later, like text or hstore.
>
> Thanks.
>
> I'm trying to do your option (a) -- defining the function return type.
>
> But I want to do this by referring to an existing table type -- which I know the returned fields must match -- rather
thanlaboriously retype the field definition list for that table.
>
> The problem is that I can't make the database accept the table type as a field definition list, when that seems like
aperfectly sensible (and in this case much more convenient) way to define the fields that will be returned.
>
> (With apologies for thoughtless top-posting in reply to Ray's earlier message).
yes -- you should be able to do this but you can't since there is no
way to transform the return type from record to type t outside the
function call. your best bet is to apply method c. above:
postgres=# create type t as (a int, b int, c int);
CREATE TYPE
postgres=# create or replace function r() returns setof text as $$
begin
return query select row(1,2,3)::t::text;
end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select r();
r
---------
(1,2,3)
(1 row)
Time: 18.000 ms
postgres=# select r()::t;
r
---------
(1,2,3)
(1 row)
Time: 0.000 ms
postgres=# select (r()::t).*;
a | b | c
---+---+---
1 | 2 | 3
(1 row)
as a bonus you can call the function more flexibly since it returns a scalar.
merlin
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
George MacKerron
Date:
> On 12/08/2011 17:26, George MacKerron wrote:
>>
>> 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.
>
> OK, fair enough.... but what exactly are you trying to do that a simple
> SELECT won't do?
>
> Ray.
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
I was writing myself an easy-to-use generic nearest-neighbour function for PostGIS that returns whole rows from the
targettable, using expanding search radii to avoid calculating distances for every geometry in the table. Of course,
thiswill hopefully become redundant in the near future with the introduction of k-nearest-neighbour GiST indices in PG
9.1.
The function is as follows (I'm no PL/pgSQL guru -- comments welcome):
create or replace function
nnrecords(
nearTo geometry
, initialDistance real
, distanceMultiplier real
, maxPower integer
, nearThings text
, nearThingsGeometryField text
, numWanted integer)
returns setof record as $$
declare
i integer;
sql text;
enough boolean;
begin
i := 0;
while i <= maxPower loop
sql := ' select count(1) >= $5 from ' || quote_ident(nearThings)
|| ' where st_dwithin($1, ' || quote_ident(nearThingsGeometryField) || ', $2 * ($3 ^ $4))';
execute sql into enough using
nearTo -- $1
, initialDistance -- $2
, distanceMultiplier -- $3
, i -- $4
, numWanted; -- $5
if enough or i = maxPower then
sql := ' select * from ' || quote_ident(nearThings)
|| ' where st_expand($1, $2 * ($3 ^ $4)) && ' || quote_ident(nearThingsGeometryField)
|| ' order by st_distance($1, ' || quote_ident(nearThingsGeometryField) || ')'
|| ' limit $5';
return query execute sql using
nearTo -- $1
, initialDistance -- $2
, distanceMultiplier -- $3
, i -- $4
, numWanted; -- $5
return;
end if;
i := i + 1;
end loop;
end
$$ language 'plpgsql' stable;
Please access the attached hyperlink for an important electronic communications disclaimer:
http://lse.ac.uk/emailDisclaimer
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
George MacKerron
Date:
On 12 Aug 2011, at 19:17, Merlin Moncure wrote: >>> you can't have it both ways. at the time the function call is >>> executed, the return type/fields must be known. you can do this by >>> either a. explicitly defining the function return type or b. >>> describing the function return type in the function call, or c. use a >>> generic type to hold the output record structure which can be >>> parsed/dealt with later, like text or hstore. >> >> I'm trying to do your option (a) -- defining the function return type. >> >> But I want to do this by referring to an existing table type -- which I know the returned fields must match -- ratherthan laboriously retype the field definition list for that table. >> >> The problem is that I can't make the database accept the table type as a field definition list, when that seems like aperfectly sensible (and in this case much more convenient) way to define the fields that will be returned. > > yes -- you should be able to do this but you can't since there is no > way to transform the return type from record to type t outside the > function call. OK, I think that answers my question. Slightly disappointing. Presumably it wouldn't be too hard to implement (?), but perhapsit's not needed often enough to be worth it. > your best bet is to apply method c. above: > > postgres=# create type t as (a int, b int, c int); > CREATE TYPE > > postgres=# create or replace function r() returns setof text as $$ > begin > return query select row(1,2,3)::t::text; > end; $$ language plpgsql; > CREATE FUNCTION > > postgres=# select r(); > r > --------- > (1,2,3) > (1 row) > > Time: 18.000 ms > postgres=# select r()::t; > r > --------- > (1,2,3) > (1 row) > > Time: 0.000 ms > postgres=# select (r()::t).*; > a | b | c > ---+---+--- > 1 | 2 | 3 > (1 row) > > as a bonus you can call the function more flexibly since it returns a scalar. > > merlin Thanks -- this looks like a decent workaround. Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer
Re: Functions returning setof record -- can I use a table type as my return type hint?
From
Pavel Stehule
Date:
Hello 2011/8/13 George MacKerron <g.j.mackerron@lse.ac.uk>: >> On 12/08/2011 17:26, George MacKerron wrote: >>> >>> 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. >> >> OK, fair enough.... but what exactly are you trying to do that a simple >> SELECT won't do? >> >> Ray. >> >> -- >> Raymond O'Donnell :: Galway :: Ireland >> rod@iol.ie > > I was writing myself an easy-to-use generic nearest-neighbour function for PostGIS that returns whole rows from the targettable, using expanding search radii to avoid calculating distances for every geometry in the table. Of course, thiswill hopefully become redundant in the near future with the introduction of k-nearest-neighbour GiST indices in PG 9.1. > > The function is as follows (I'm no PL/pgSQL guru -- comments welcome): > > create or replace function > nnrecords( > nearTo geometry > , initialDistance real > , distanceMultiplier real > , maxPower integer > , nearThings text > , nearThingsGeometryField text > , numWanted integer) > returns setof record as $$ > declare > i integer; > sql text; > enough boolean; > begin > i := 0; > while i <= maxPower loop > sql := ' select count(1) >= $5 from ' || quote_ident(nearThings) > || ' where st_dwithin($1, ' || quote_ident(nearThingsGeometryField) || ', $2 * ($3 ^ $4))'; > execute sql into enough using > nearTo -- $1 > , initialDistance -- $2 > , distanceMultiplier -- $3 > , i -- $4 > , numWanted; -- $5 > if enough or i = maxPower then > sql := ' select * from ' || quote_ident(nearThings) > || ' where st_expand($1, $2 * ($3 ^ $4)) && ' || quote_ident(nearThingsGeometryField) > || ' order by st_distance($1, ' || quote_ident(nearThingsGeometryField) || ')' > || ' limit $5'; > return query execute sql using > nearTo -- $1 > , initialDistance -- $2 > , distanceMultiplier -- $3 > , i -- $4 > , numWanted; -- $5 > return; > end if; > i := i + 1; > end loop; > end > $$ language 'plpgsql' stable; > * move non necessary rows from cycle. * use a statement FOR instead WHILE * flag STABLE is wrong, your function is VOLATILE Regards Pavel Stehule > > Please access the attached hyperlink for an important electronic communications disclaimer: http://lse.ac.uk/emailDisclaimer > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >