plperl patch

I know it's late in the day, but ...

Attached is a patch and 2 replacement files for plperl. The work has
been done under the auspices of the plperlng project on pgfoundry. The
code (which has been through several iterations) comes from
CommandPrompt, and has had some minor editorializing by me (spelling,
indentation, function heading comments). It has also been reviewed
somewhat by Abhijit Menon-Sen, who supplied a small optimization. It has
been tested by me and by David Fetter.

These changes should have low impact - they will not affect anyone who
doesn't use plperl, nor anyone who does not use the new functionality
(with one possible tiny exception, noted below).

This functionality does not by any means represent all we want to
achieve with plperl, but it is what we have been able to achieve in a
short space of time, and is a significant advance. If the patch is
accepted we will provide a doc patch very shortly.

Summary of new functionality (for more details see below):
. Shared data space and namespace. There is a new global variable
%_SHARED that functions can use to store and save data between
invocations of a function, or between different functions. Also, all
trusted plperl function now share a common Safe container (this is an
optimization, also), which they can use for storing non-lexical
variables, functions, etc.
. Triggers are now supported
. Records can now be returned (as a hash reference)
. Sets of records can now be returned (as a reference to an array of
hash references).
. new function spi_exec_query() provided for performing db functions or
getting data from db.

Backwards compatibility: the move to a common name space for trusted
functions will affect anyone who now relies on the fact that each
function has its own namespace and has different functions store
different data with the same name - I expect this to be at most some
number of users countable on the fingers of one hand.

Known limitations - both of these will change in a future release:
. 'setof record' is not supported
. arrays and embedded composite types are represented as strings, and
must be returned as strings.

Files changed:
GNUmakefile
SPI.xs
plperl.c (this is where all the interesting stuff happens)

Files deleted:
eloglvl.c
eloglvl.h

Files added (incorporating eloglvl functionality):
spi_internal.c
spi_internal.h

More detailed description of new functionality, with some examples:

Database Access from PL/perl

PL/perl provides a function called spi_exec_query. Calling spi_exec_query
with a query string and an optional limit argument causes that query to be
run and the result to be returned in a result reference to hash.

The result has two values:
rows which returns either the number of rows returned by the query, or
in the case of a SELECT query, a reference to an array of the actual rows
which can be accessed by row number and column name,
and status which is the SPI_exec() return value.

The result hash can be modified.

For example:

$rv = spi_exec_query(''SELECT * FROM my_table'', 5);

returns up to 5 rows from my_table.
If my_table has a column my_column, it would be accessed as

$foo = $rv->{rows}[$i]->{my_column};
$nrows = @{$fv->{rows}};

or

$query = "INSERT INTO my_table VALUES (1, ''test'')";
$rv = spi_exec_query($query);

result would be accessed as

$res = $rv->{status}; //SPI_OK_INSERT in our example
$nrows = $rv->{rows};
To return a row or composite-type value from a PL/perl-language
function, you can use hash:

CREATE TYPE __testrowperl AS (f1 integer, f2 text, f3 text);

CREATE OR REPLACE FUNCTION perl_row() RETURNS __testrowperl AS $$

return {f2 => 'hello', f1 => 1, f3 => 'world'};

$$ LANGUAGE plperl;

select * FROM perl_row();

Will return:
f1 | f2 | f3
----+-------+-------
1 | hello | world

You can also use SETOF functions (returning sets (multiple rows)):

CREATE TYPE __testsetperl AS (f1 integer, f2 text, f3 text);

CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF __testsetperl AS $$
return
[
{f1 => 1, f2 => 'hello', f3 => 'world'},
{f1 => 2, f2 => 'hello', f3 => 'postgres'},
{f1 => 3, f2 => 'hello', f3 => 'plperl'}
];
$$ LANGUAGE plperl;

SELECT * FROM perl_set();

Will return:
f1 | f2 | f3
----+-------+----------
1 | hello | world
2 | hello | postgres
3 | hello | plperl

CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF __testsetperl AS $$
return[];
$$ LANGUAGE plperl;

SELECT * FROM perl_set();

Will return:
f1 | f2 | f3
----+-------+----------

CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF __testsetperl AS $$
return[{f1=>undef, f2=>test, f3=>undef}];
$$ LANGUAGE plperl;

SELECT * FROM perl_set();

Will return:
f1 | f2 | f3
----+-------+----------
| test |
You can use hash %_SHARED to store data between function calls.

For exaple:

CREATE OR REPLACE FUNCTION set_var() RETURNS text AS $$
$_SHARED{first}='hello plperl';
return 'ok';
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_var() RETURNS text AS $$
return $_SHARED{first};
$$ LANGUAGE plperl;

SELECT set_var('hello plperl');
SELECT get_var(); //will return "hello plperl" in our example

Trigger Procedures in PL/Perl

When a function is used in a trigger, the hash reference $_TD contains
trigger-related values.

$_TD->{"new"}
A hash containing the values of the new table row for INSERT/UPDATE
actions, or empty for DELETE. Fields that are NULL will be undefined!

$_TD->{"old"}
A hash containing the values of the old table row for UPDATE/DELETE
actions, or empty for INSERT. Fields that are NULL will be undefined!

$_TD->{"name"}
contains the trigger name.

$_TD->{"event"}
contains the event as a string (INSERT, UPDATE, DELETE or UNKNOWN).

$_TD->{"when"}
contains one of BEFORE, AFTER or UNKNOWN.

$_TD->{"level"}
contains one of ROW, STATEMENT or UNKNOWN.

$_TD->{"relid"}
contains the relation ID of the table on which the trigger occurred.

$_TD->{"relname"}
contains the relation name.

$_TD->{"argc"}
contains the arguments count.

If the trigger was called with arguments they are available as
$_TD->{"args"}[0], ... , $_TD->{"args"}[($_TD->{"argc"}-1)]

Modification control

Structure of table for examples:
CREATE TABLE test (
i int,
v varchar
) WITH OIDS;

Example of ON INSERT/UPDATE TRIGGER:

CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
if (($_TD->{new}{i}>=100) || ($_TD->{new}{i}<=0))
{
return "SKIP"; # Skip INSERT/UPDATE command
}
elsif ($_TD->{new}{v} ne "immortal")
{
$_TD->{new}{v} .= "(modified by trigger)";
return "MODIFY"; # Modify tuple and proceed
INSERT/UPDATE command
}
else
{
return; # Proceed INSERT/UPDATE command
}
$$ LANGUAGE plperl;

CREATE TRIGGER test_valid_id_trig BEFORE INSERT OR UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE valid_id();

INSERT INTO test (i, v) VALUES (1,'first line');
INSERT INTO test (i, v) VALUES (2,'second line');
INSERT INTO test (i, v) VALUES (3,'third line');
INSERT INTO test (i, v) VALUES (4,'immortal');

INSERT INTO test (i, v) VALUES (101,'bad id');
will output INSERT 0 0;

UPDATE test SET i = 5 where i=3;
will output UPDATE 1, but

UPDATE test SET i = 100 where i=1;
will output UPDATE 0

Example of ON DELETE TRIGGER:

CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$
if ($_TD->{old}{v} eq $_TD->{args}[0])
{
return "SKIP"; # Skip DELETE command
}
else
{
return; # Proceed DELETE command
};
$$ LANGUAGE plperl;

CREATE TRIGGER immortal_trig BEFORE DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE immortal('immortal');

INSERT INTO test (i, v) VALUES (1,'first line');
INSERT INTO test (i, v) VALUES (2,'second line');
INSERT INTO test (i, v) VALUES (3,'third line');
INSERT INTO test (i, v) VALUES (4,'immortal');

DELETE FROM test;
will output: DELETE 3, and line #4 has not been deleted

Tip: If the TD->{"when"} is BEFORE, you may return "SKIP" to abort the
event,"MODIFY" to indicate you've modified the row, or "undef" to make
the action.

Tip: To return "undef" just specify "return;" in plperl function source.

enjoy

andrew