Does anybody have an example with spi_prepare, spi_exec_prepared or
spi_query_prepared? I managed to get the basic concepts:



create function test_pl(varchar) returns setof varchar as $$
my ($job)=@_;
my $qry="select ename from emp where job='".$job."'";
my $sth=spi_query($qry);
while (my $row=spi_fetchrow($sth)) {
last unless defined($row);
return_next($row->{ename});
}
return(undef);
$$ language plperl;

Strangely enough, that even works:


mgogala@nycwxp2622:~$ psql -U scott -h localhost
Password for user scott:
psql (8.4.1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

scott=> select * from (select test_pl('CLERK')) ename order by 1;
test_pl
---------
ADAMS
JAMES
MILLER
SMITH
(4 rows)

scott=>


Unfortunately, this is slicing & dicing SQL from the text strings,
precisely what I keep telling my developers not to do. Does anybody here
have a small example with the functions above and binding parameters? It
would save me the time necessary to figure it out by myself, using the
trial & error method.

--
http://mgogala.byethost5.com

On Wed, 09 Dec 2009 23:22:13 +0000, Mladen Gogala wrote:

This is no longer an issue. I figured it out:

create function test_pl(varchar) returns setof varchar as $$
my ($job)=@_;
my $qry='select ename from emp where job=$1';
my $sth=spi_prepare($qry,'varchar');
my $rs=spi_query_prepared($sth,$job);
while (my $row=spi_fetchrow($rs)) {
last unless defined($row);
return_next($row->{ename});
}
return(undef);
$$ language plperl;


--
http://mgogala.byethost5.com