Thread: Need help in spi_prepare errors

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

paresh masani <masaniparesh@gmail.com> writes:
> Below function doesn't work: (I tried each combination mentioned with
> # but none of them working.)

I haven't tried it, but a look at the code makes me think that
spi_prepare wants each type name to appear as a separate argument.
It definitely won't work to smash them all into one string like that.

You're going to have the same problem at spi_exec_prepared --- it
thinks each actual value should be a separate argument.

I'm not much of a Perl hacker, but I seem to recall that it's possible
to pass an array to a function in a way that will make the array
elements look like separate arguments.  If you really need a dynamic
list of types and values, maybe there's some solution in that direction.

            regards, tom lane

Tom Lane escribió:

> I'm not much of a Perl hacker, but I seem to recall that it's possible
> to pass an array to a function in a way that will make the array
> elements look like separate arguments.  If you really need a dynamic
> list of types and values, maybe there's some solution in that direction.

Actually any time you pass an array as a parameter, the list is
flattened and the function sees it as a plain list.  If that doesn't
seem to make sense, consider that if you pass two lists they will be
flattened to a single list and you won't be able to tell where one
ends and the other starts.

If you really want two separate lists, you need to pass them as array
references (i.e. \@myarray).  I guess you could also pass an element
count but that's not very perlish.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

You were correct below trigger worked. Giving reference for others.

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 $query = "INSERT INTO mytable  $raw_row VALUES (\$1, \$2)";
       my $prepared = spi_prepare($query, @col_types);
       spi_exec_prepared($prepared, @new_row);
       return "success";
$$ LANGUAGE plperl;

Thanks, Paresh

On 10/7/09, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Tom Lane escribió:
>
>> I'm not much of a Perl hacker, but I seem to recall that it's possible
>> to pass an array to a function in a way that will make the array
>> elements look like separate arguments.  If you really need a dynamic
>> list of types and values, maybe there's some solution in that direction.
>
> Actually any time you pass an array as a parameter, the list is
> flattened and the function sees it as a plain list.  If that doesn't
> seem to make sense, consider that if you pass two lists they will be
> flattened to a single list and you won't be able to tell where one
> ends and the other starts.
>
> If you really want two separate lists, you need to pass them as array
> references (i.e. \@myarray).  I guess you could also pass an element
> count but that's not very perlish.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>