pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / 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>
  2002-05-03 22:24 ` Re: Returning composite types from a plpgsql function Tom Lane <tgl@sss.pgh.pa.us>
  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 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>
  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