BUG #3415: plperl spi_exec_prepared variable undef value confusion

The following bug has been logged online:

Bug reference: 3415
Logged by: Matt
Email address: matt(at)lindenelevator(dot)com
PostgreSQL version: 8.2.3
Operating system: ubuntu 7.04, gentoo 2007.0
Description: plperl spi_exec_prepared variable undef value confusion
Details:

When inserting a null timestamp from a variable, I encounter the following:
ERROR: error from Perl function: invalid input syntax for type timestamp:
""

To replicate the problem
1. Prepare a statement: spi_prepare(...),
2. Set a variable: my $var = ...,
3. Re-set the variable's value: $var = undef,
4. Execute the prepared statement: spi_exec_prepared(...)

Matt Taylor

The following code should recreate the problem:

create table bug_demo_table ( x timestamp );

create function bug_demo() returns integer as $$

use strict;
use Data::Dumper;

# prepare the statement
my $sql = 'insert into bug_demo_table ( x ) ';
$sql .= 'values ( $1 );' ;
my $sth = spi_prepare( $sql, 'timestamp' );

# first set the variable to some appropriate value
my $var = '2007-01-01 01:01:01.000';
elog(NOTICE, "\n". Dumper($var). "\n");

# set the variable to undef
$var = undef; # fails
elog(NOTICE, "\n". Dumper($var). "\n");

# re-initialize the variable and set it to undef
# uncomment this line to prevent the error
#my $var = undef; # works

spi_exec_prepared( $sth, $var ); # fails

return 1;

$$ LANGUAGE 'plperlu';

select bug_demo();

select * from bug_demo_table;

drop table bug_demo_table cascade;
drop function bug_demo() cascade;