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 -- 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. > > 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 perhaps it'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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
