Need help in spi_prepare errors

From: paresh masani <masaniparesh(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need help in spi_prepare errors
Date: 2009-10-07 05:48:47
Message-ID: a9d94e070910062248r33ba5651va6f851cd3f4d8c1b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stuart Bishop 2009-10-07 06:53:21 Re: attempted to lock invisible tuple - PG 8.4.1
Previous Message APseudoUtopia 2009-10-07 01:12:18 Re: Vacuumdb Fails: Huge Tuple