| Subject: | Unable to use CREATE FUNCTION with parameters |
When executing the attached script on Linux 2.4.20 i686 with perl 5.6.1 using DBD::Pg version 1.43, i get the error
DBD::Pg::st execute failed: execute called with an unbound placeholder at ./dbd_pg_create_function_bug.pl line 10.
because DBD::Pg seems to think that the $1 references a placeholder in the current statement, rather than a reference to an function argument.
#! /usr/bin/perl -wT
use DBI();
use strict;
my ($dbh) = DBI->connect('dbi:Pg:dbname=blah', 'user', 'pass', { 'PrintError' => 0, 'RaiseError' => 1 }) || die("Failed to connect");
my ($sql) = 'CREATE FUNCTION FROM_UNIXTIME(integer) RETURNS timestamp WITH TIME ZONE AS $$ SELECT CAST(\'epoch\' AS timestamp WITH TIME ZONE) + CAST($1 || \' seconds\' AS interval) $$ LANGUAGE SQL';
my ($sth) = $dbh->prepare($sql);
$sth->execute();
$sth->finish();
$sql = 'DROP FUNCTION FROM_UNIXTIME(integer)';
$sth = $dbh->prepare($sql);
$sth->execute();
$sth->finish();