The following bug has been logged online:
Bug reference: 3415
Logged by: Matt
Email address: matt@lindenelevator.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;