This web site is partly supported by these ads:

---

Defindit Docs and Howto Home

This page last modified: Jun 08 2007

title:PLPerl Notes
description:Brief notes about Perl inside PostgreSQL
keywords:plsql,perl,plperl,pl,postgres,sql

To see a full working example of this code, download the Perl SQL
example and see pal.pl which is in the tar file:

http://defindit.com/readme_files/perl_sql_example.tar

There are additional notes included in the example files. The example
does not create the 20,000 record table, so you'd need to do a little
extra work to reproduce my results.

PostgreSQL (aka Postgres) has support available for Perl as an
internal language. This is a neat idea, but it turns out that
comparing carefully crafted examples, normal Perl and DBI is
faster. Below is most of the code I used. The parts you don't see are
standard DBI connection, etc. I'll post those later (they are also
components of my open source Bioinformatics code).

Thanks to Andrew Dunstan for his example PL/Perl at:

http://www.oreillynet.com/pub/a/databases/2005/11/10/using-perl-in-postgresql.html


I created a little demo table with around 20,000 records. Run times
were 16 or 17 seconds for the Perl DBI subroutine, and 18 or 19
seconds for the PL/Perl version.


-- My working version of the PL/Perl function.
-- I didn't try the trinary operator, but speed should be unchanged.
-- (I think the trinary operator is hard to read)
create or replace function palindrome(text) returns boolean 
language plperl 
immutable
as $$

  my $canonical = lc($_[0]);
  $canonical =~ s/\s+//g;
  if ($canonical eq reverse($canonical))
  {
    return "true";
  }
  return "false";
$$;


# This is the Perl DBI used to call the PL/Perl function in a SQL
# query.

sub sql_pal_internal
{
    my $q_name = "sql_pal_internal";
    my $dbh = get_db_handle("demo");

    my $sql = "select * from demo where palindrome(name)";
    my $sth = $dbh->prepare($sql);
    if ($dbh->err()) { die "$q_name 1\n$DBI::errstr\n"; }
    $sth->execute();
    if ($dbh->err()) { die "$q_name 2\n$DBI::errstr\n"; }
    my %pal_hash;
    while((my $de_pk, my $name) = $sth->fetchrow_array())
    {
	$pal_hash{$de_pk} = $name;
    }
    return %pal_hash;
}



Below is the Perl DBI subroutine I compared the PL/Perl function
to. The PL/Perl is called from a similar subroutine so we'd be
comparing apples-to-apples in a more or less realy world example.

sub sql_palindrome
{
    my $q_name = "sql_palindrome";
    my $dbh = get_db_handle("demo");

    my $sql = "select * from demo";
    my $sth = $dbh->prepare($sql);
    if ($dbh->err()) { die "$q_name 1\n$DBI::errstr\n"; }
    $sth->execute();
    if ($dbh->err()) { die "$q_name 2\n$DBI::errstr\n"; }
    my %pal_hash;
    while((my $de_pk, my $name) = $sth->fetchrow_array())
    {
	my $canonical = lc($name);
	$canonical =~ s/\s+//g;
	if ($canonical eq reverse($canonical))
	{
	    $pal_hash{$de_pk} = $name;
	}
    }
    return %pal_hash;
}