Perl in Postgres tips

I recently needed to convert the type of a Postgres text field from varchar to date. There would probably have been a pure SQL solution, but since using Perl in Postgres had been so easy last time I tried, it seemed like the obvious and simple solution to use Perl again.

However, while writing the text2date function below, I lost quite some time with a few little traps of plperl. So here is my short list of tips for using PL/Perl in PostgreSQL:

• All examples suggest using $$ as a delimiter in function definitions, but that only works in recent versions of PostgreSQL. With 7.4 - the version currently in Debian stable (Sarge) - you cannot use $$ and need single quotes instead.
• When using single quotes, backslashes must be escaped (doubled) in the Perl code. You will get strange results if you forgot that.
• To see the code as Postgres actually stored it:
  SELECT prosrc FROM pg_proc WHERE proname = 'YourPerlFunction';
  
• warn doesn't work. Use elog(level, msg) instead. The documentation for elog says:

elog(level, msg)

Emit a log or error message. Possible levels are DEBUG, LOG, INFO, NOTICE, WARNING, and ERROR.
ERROR raises an error condition; if this is not trapped by the surrounding Perl code, the error propagates out to the calling query, causing the current transaction or subtransaction to be aborted. This is effectively the same as the Perl die command.

Finally, here is the text2date function I used:

CREATE OR REPLACE FUNCTION text2date(text) RETURNS "date" AS '
 $_=shift;
 my ($day, $month, $year) = /^\\s* (\\d{1,2}) [^\\d]+ (\\d{1,2}) [^\\d]+ (\\d{4}) .* /x;
 if ( !($day && $month && $year) ) {
   elog(WARNING, "No date found in $_\\n");
   return undef;
 }
 elsif ($day > 31 || $month > 12 || length($year) != 4) {
   elog(WARNING, "Wrong date: $year-$month-$day\\n");
   return undef;
 }
 return "$year-$month-$day";
' LANGUAGE 'plperl' IMMUTABLE STRICT;

And this is how it was stored in Postgres:

# SELECT prosrc FROM pg_proc WHERE proname = 'text2date';

 $_=shift;
 my ($day, $month, $year) = /^\s* (\d{1,2}) [^\d]+ (\d{1,2}) [^\d]+ (\d{4}) .* /x;
 if ( !($day && $month && $year) ) {
   elog(WARNING, "No date found in $_\n");
   return undef;
 }
 elsif ($day > 31 || $month > 12 || length($year) != 4) {
   elog(WARNING, "Wrong date: $year-$month-$day\n");
   return undef;
 }
 return "$year-$month-$day";

Obviously, this is for European style dates.