plperl Notes

Back to Geoffrey's PostgreSQL Notes

The following are a couple of examples of how to create a stored procedure written in Perl. This stuff was stolen from the documentation for PostgreSQL 7.0beta2.

Make the language available

This only needs to be done once. You need to find plperl.so and put it in the appropriate directory (it should have been built with Postgres, but you might need to do a 'make install' in the src/pl/plperl directory).

CREATE FUNCTION plperl_call_handler() RETURNS opaque
AS '/usr/local/pgsql/lib/plperl.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plperl'
HANDLER plperl_call_handler
LANCOMPILER 'PL/Perl';

Add two numbers demo

Demonstration of how to create and use a simple stored procedure written in Perl:

CREATE FUNCTION addints(int4, int4) RETURNS int4 AS '
  return $_[0] + $_[1]
' LANGUAGE 'plperl';

SELECT addints(3, 4);

Passing tuples demo

Demonstration of how to pass a tuple to a stored procedure:

CREATE TABLE twoints ( a integer, b integer);
INSERT INTO twoints VALUES (1, 2);
INSERT INTO twoints VALUES (10, 15);

CREATE FUNCTION addtwoints(twoints) RETURNS integer AS '
  $tup = shift;
  return $tup->{"a"} + $tup->{"b"};
' LANGUAGE 'plperl';

SELECT addtwoints(twoints) from twoints;

Safe Perl

Demonstrating that plperl won't let you do bad things:

CREATE FUNCTION badfunc() RETURNS int4 AS '
  open(TEMP, ">/tmp/badfile");
  print TEMP "Gotcha!\n";
  return 1;
' LANGUAGE 'plperl';

SELECT badfunc();

Cleaning up

DROP FUNCTION plperl_call_handler();
DROP PROCEDURAL LANGUAGE 'plperl';
DROP FUNCTION addints(int4, int4);
DROP FUNCTION addtwoints(twoints);
DROP TABLE twoints;
DROP FUNCTION badfunc();

Back to Geoffrey's PostgreSQL Notes