[BUGS] BUG #3415: plperl spi_exec_prepared variable undef value confusion

[Matt]

The following bug has been logged online:

Bug reference:      3415
Logged by:          Matt
Email address:      [EMAIL PROTECTED]
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;

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend