pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Returning composite types from a plpgsql function 2+ messages / 2 participants [nested] [flat]
* Returning composite types from a plpgsql function @ 2002-05-03 21:24 Joseph Barillari <jbarilla@princeton.edu> 0 siblings, 1 reply; 2+ messages in thread From: Joseph Barillari @ 2002-05-03 21:24 UTC (permalink / raw) To: pgsql-sql Hi, I've read in the mailing list archives that plpgsql does not support assignment to array variables, so it is impossible to use them to return data from a plpgsql function. I'm curious as to how one might return more than one base type from such a function, short of creating a temporary table and inserting the value into that. For example: CREATE TABLE tmp (a INTEGER, b INTEGER); --define the type CREATE OR REPLACE FUNCTION frob() RETURNS tmp AS ' DECLARE ret tmp%ROWTYPE; BEGIN ret.a := 1; ret.b := 2; return ret; END;' LANGUAGE 'plpgsql'; Unfortunately, when I execute this, I get: cal=> select frob(); frob ----------- 172795360 (1 row) Is there some sort of type coercion I have to do to get this to work? Thanks, Joe ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Returning composite types from a plpgsql function @ 2002-05-03 22:24 Tom Lane <tgl@sss.pgh.pa.us> parent: Joseph Barillari <jbarilla@princeton.edu> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2002-05-03 22:24 UTC (permalink / raw) To: Joseph Barillari <jbarilla@princeton.edu>; +Cc: pgsql-sql Joseph Barillari <jbarilla@princeton.edu> writes: > Unfortunately, when I execute this, I get: > cal=3D> select frob(); > frob=20=20=20=20 > ----------- > 172795360 > (1 row) You can't do anything useful with a rowtype function result except select a single field from it. The manual's discussion for SQL functions also applies to PL-language functions: http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/xfunc-sql.html#AEN26354 There is work afoot to allow SELECT ... FROM function(...) which would make functions returning rowtypes a whole lot more useful. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-03 22:24 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2002-05-03 21:24 Returning composite types from a plpgsql function Joseph Barillari <jbarilla@princeton.edu> 2002-05-03 22:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox