[postgis-users] Returning multiple fields from a plpgsql function

[Paragon Corporation]

Steve,

Did you try 

SELECT (my_function(x,y)).*

That's the way we usually do it.

Also you might want to consider using OUT paramaters.  If you use OUT
paramaters, then you don't need to create a type specifically for your
function.

so an example (I believe OUT parameters have been supported since 8.2 though
this is the 8.5 dev help)


exercpted from here
http://developer.postgresql.org/pgdocs/postgres/xfunc-sql.html

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

 SELECT * FROM sum_n_product(11,42);
 sum | product
-----+---------
  53 |     462


Leo
 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
Woodbridge
Sent: Wednesday, July 29, 2009 2:19 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Returning multiple fields from a plpgsql function

Hi,

This is another slightly off topic, but maybe generally helpful inquiry.

I have a plpgsql function the does some spatial queries and returns results
using a type like:

CREATE TYPE my_result AS
     (attr1 text,
      attr2 text,
      attr3 text,
      distance float,
      linkid int);


in the function:

DECLARE
     ret my_result;

then set the values of the fields like:

     ret.attr1 := attr1;
     ...

and return the results like:

     return ret;


later I do:

select my_function(x,y);

and get a result that looks like:

("attr1","attr2","attr3",102.2,1234567)

which all looks fine and dandy, except that I can not seem to access the
individual fields. So for example if I want the value of my_result.attr1 how
would I get that? Is there a better way to do this?

Thanks,
   -Steve
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4289 (20090729) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com