Thread: plperl spi_prepare and arrays

plperl spi_prepare and arrays

From
Nathan Jahnke
Date:
Hi all,

Spent the last few hours searching but was unable to get a
satisfactory answer. Basically, if I do this:

my $arr = [1,2,3,4];
$_SHARED{test} = spi_prepare('update users set interest=$1 where
uid=2', 'int[]');
spi_exec_prepared($_SHARED{test}, $arr);

I get:

DBD::Pg::st execute failed: ERROR:  error from Perl function "view":
array value must start with "{" or dimension information

Okay, so I can pass the arrayref to spi_exec_prepared by making a
string out of it, with join() and string concatenation with curlies
and all that jazz, but this is an inefficient pain, especially if I
happened to have more than a 1D array. Shouldn't I be able to pass an
arrayref directly?

Also, what about going the other way - getting postgres arrays in
plperl as arrayrefs instead of as strings with curlies? In searching I
found this old message:

http://archives.postgresql.org/pgsql-general/2006-08/msg01472.php

... which suggests to me that it is indeed possible, but I would like
more details.

Thank you very much.


Nathan