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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200707062008
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

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