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

[Stephen Woodbridge]

Leo and David,

Thanks! Both of these ideas are really cool. I have not seen either of 
them in use before. I just recently made the jump from 8.1 to 8.3 and 
have not had time to check out all of the new features. I have got to 
make more time to read up on this stuff.

It sounds like the (my_function(x,y)).* will likely be more backwards 
compatible, but I like the OUT params as being a cleaner implementation 
and it is a pain to have to create and manage the types if you have a 
lot of functions like this, especially when you want to redefine the 
type, you have to drop cascade the type and then reload everything that 
referenced it.

Many thanks guys,
   -Steve



Paragon Corporation wrote:
> 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
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users