In 8.1.3, a PL/Perl function that returns 'SETOF',
when used in the SELECT list of a query, generates the following error:
"set-valued function called in context that cannot accept a set"
Hence there does not seem to be any way of using a PL/Perl function
that returns 'SETOF' on non-constant input
(e.g. on a set of table rows generated by the FROM clause of a query).
The DNS data for our our network, consisting of several thousand machines,
is stored as one row per series of machines.
I have a PL/Perl function that converts a row of the table
to a set of composite type, one per machine.
It worked fine on 8.0.3.
Below is code that illustrates the problem.
It shows functions in both SQL and PL/Perl that take as input some table rows
and return 'SETOF int'.
The SQL function works fine on 8.0.3 and 8.1.3.
The PL/Perl function works fine on 8.0.3.
The error occurs only for the PL/Perl function on 8.1.3.
-------------------------------------------------------------------------------
CREATE TABLE mytable (n int);
COPY mytable FROM stdin;
1
2
3
\.
CREATE FUNCTION mysqlfunc(mytable) RETURNS SETOF int AS $$
SELECT $1.n
$$ LANGUAGE SQL;
CREATE FUNCTION myperlfunc(mytable) RETURNS SETOF int AS $$
return [$_[0]->{n}];
$$ LANGUAGE plperl;
SELECT mysqlfunc(mytable.*) from mytable where n=2;
SELECT myperlfunc(mytable.*) from mytable where n=2;
-------------------------------------------------------------------------------
With VERBOSITY set to verbose, error message is:
ERROR: 0A000: set-valued function called in context that cannot accept a set
LOCATION: plperl_func_handler, plperl.c:1029
I have tested only on FreeBSD-6.1.
Chana Slutzkin
CS System Group
Hebrew University
Jerusalem, Israel
P.S. I posted the bug a few days ago as BUG #2426 but it received no response.
I hope I have expressed myself more clearly this time.