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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: George MacKerron <g(dot)j(dot)mackerron(at)lse(dot)ac(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Functions returning setof record -- can I use a table type as my return type hint?
Date: 2011-08-12 18:17:16
Message-ID: CAHyXU0yERxNGjgLRRyzUbanRT0VWp+HLH10ZsEDeN-uJj07UEw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Aug 12, 2011 at 12:01 PM, George MacKerron
<g(dot)j(dot)mackerron(at)lse(dot)ac(dot)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 than 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 a perfectly 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vincent Veyron 2011-08-12 18:31:06 Re: Indicating DEFAULT values in INSERT statement
Previous Message Rich Shepard 2011-08-12 18:03:15 Re: COPY from .csv File and Remove Duplicates