CREATE OR REPLACE FUNCTION delayed_return (integer)
RETURNS setof integer
LANGUAGE plperl
AS $$
my $foo = $_[0];
my $bar = $foo/2;
my $sql = “select * from generate_series(1,$foo);”;
my $sth = spi_query($sql);
my @sb;
$sql2 = “select * from generate_series (3,$bar) WHERE generate_series IN (0”;
while (defined ($row = spi_fetchrow($sth))) {
elog(NOTICE, “SB1-row_number::$row->{generate_series}”);
@sb = (@sb, $row->{generate_series});
if ($row->{generate_series}%2 == 0){
$sql2 = $sql2 . “,$row->{generate_series}”;
};
};
$sql2 = $sql2 . ‘);’;
elog(NOTICE,”$sql2”);
my $sth2 = spi_query($sql2);
my @ex;
while (defined ($row2 = spi_fetchrow($sth2))) {
elog(NOTICE, “EX-row_number::$row2->{generate_series}”);
@ex = (@ex,$row2->{generate_series});
};
foreach $b (@sb) {
if (grep {$_ eq $b } @ex ) {
elog(NOTICE, “SB2-row_number::$b”);
return_next($b);
};
};
return;
$$;
davinci=# SELECT * FROM delayed_return(18);
NOTICE: SB1-row_number::1
NOTICE: SB1-row_number::2
NOTICE: SB1-row_number::3
NOTICE: SB1-row_number::4
NOTICE: SB1-row_number::5
NOTICE: SB1-row_number::6
NOTICE: SB1-row_number::7
NOTICE: SB1-row_number::8
NOTICE: SB1-row_number::9
NOTICE: SB1-row_number::10
NOTICE: SB1-row_number::11
NOTICE: SB1-row_number::12
NOTICE: SB1-row_number::13
NOTICE: SB1-row_number::14
NOTICE: SB1-row_number::15
NOTICE: SB1-row_number::16
NOTICE: SB1-row_number::17
NOTICE: SB1-row_number::18
NOTICE: select * from generate_series (3,9) WHERE generate_series IN (0,2,4,6,8,10,12,14,16,18);
NOTICE: EX-row_number::4
NOTICE: EX-row_number::6
NOTICE: EX-row_number::8
NOTICE: SB2-row_number::4
NOTICE: SB2-row_number::6
NOTICE: SB2-row_number::8
delayed_return
—————-
4
6
8
(3 rows)
Delayed Returns With Plperl
The other day I ran into a roadblock in a function I wanted to refactor. What I needed to do was execute a query, followed by another query that was based on the first queries output, and then return the results of the first query based upon the outcome of the second query. A little convoluted sure, but sometimes that’s what you need. In any case it turns out theres really no way to do this in plpgsql; looping through a stored recordset is pretty much out of the question without using temp tables. I also thought about using pltcl, but unfortunatly it doesn’t support SRF’s, so that was out too. The following is my rough “proof of concept” function written in plperl, which seemed the language best suited to getting this done. (I’d be interested in other languages though)