Hi, I have been getting strange behaviors in using spi_prepare and
spi_exec_prepared. The below function works fine (every thing is hard
coded):
CREATE OR REPLACE FUNCTION init() RETURNS TEXT AS $$
my $prepared = spi_prepare("INSERT INTO mytable (\"col1\", \"col2\")
VALUES (\$1, \$2)", "integer", "character varying");
spi_exec_prepared($prepared, '5', '6');
return "success";
$$ LANGUAGE plperl;
Below function doesn't work: (I tried each combination mentioned with
# but none of them working.)
CREATE OR REPLACE FUNCTION init() RETURNS TEXT AS $$
my $raw_row = "(\"col1\", \"col2\")";
my $new_row = "'5', '6'";
#my $col_types = "'integer', 'character varying'";
#my $col_types = "\"integer\", \"character varying\"";
my $col_types = '"integer", "character varying"';
my $query = "INSERT INTO mytable $raw_row VALUES (\$1, \$2)";
my $prepared = spi_prepare($query, $col_types);
#my $prepared = spi_prepare("INSERT INTO mytable (\"col1\", \"col2\")
VALUES (\$1, \$2)", "integer", "character varying");
spi_exec_prepared($prepared, $new_row);
return "success";
$$ LANGUAGE plperl;
Errors:
sysdb=# select init();
ERROR: error from Perl function "init": syntax error at or near
"'integer'" at line 8.
sysdb=# select init();
ERROR: error from Perl function "init": invalid type name ""integer",
"character varying"" at line 8.
If I hard code spi_prepare arguments, the below error occures in
spi_exec_prpepared.
sysdb=# select init();
ERROR: error from Perl function "init": spi_exec_prepared: expected 2
argument(s), 1 passed at line 10.
Any help would be much helpful.
Thanks, Paresh