Binding....
Mladen Gogala
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.
Mladen Gogala
> Does anybody have an example with spi_prepare, spi_exec_prepared or
> spi_query_prepared? I managed to get the basic concepts:
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;