Re: Capturing return value of a function in Pl/Perl

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> I'm calling a plpgsql function in a plperl function.
> plpgsql function is a simple select query returning a single row of records.
> How can i retrieve the values of different fields in my plperl function?

Please check out the page:

http://www.postgresql.org/docs/current/interactive/plperl-database.html

Here is a sample function to demonstrate what spi_exec_query returns:

CREATE FUNCTION readarow()
RETURNS TEXT
LANGUAGE plperlu
AS $_$

use strict;
use warnings;
use Data::Dumper;

my $SQL = "SELECT * FROM pg_stat_activity LIMIT 1";
my $result = spi_exec_query($SQL);

return Dumper $result;

$_$;

Here is some sample output:

greg=# select * from readarow();
                                  readarow
- ----------------------------------------------------------------------------
 $VAR1 = {
           'status' => 'SPI_OK_SELECT',
           'processed' => 1,
           'rows' => [
                       {
                         'usesysid' => '10',
                         'datname' => 'greg',
                         'datid' => '16398',
                         'waiting' => 'f',
                         'current_query' => 'select * from readarow();',
                         'backend_start' => '2007-07-06 20:07:53.22277-04',
                         'query_start' => '2007-07-06 20:08:15.37116-04',
                         'client_port' => '-1',
                         'client_addr' => undef,
                         'usename' => 'greg',
                         'procpid' => '2396'
                       }
                     ]
         };



- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200707062008
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGjtmfvJuQZxSWSsgRA1cHAKDXqc3JKwIJkGrNjCqbmRcZY+SUAQCfYIiW
BwBuOZg4uZmC8DknrHPbtME=
=8tPe
-----END PGP SIGNATURE-----