DBD::Pg - send arrays to a stored procedure

From: Dmitry Karasik (dmitry_at_karasik.eu.org)
Date: 01/17/05 

Date: Mon, 17 Jan 2005 15:05:51 +0100
To: dbi-users@perl.org

Hello,

After I've upgraded to newest versions of DBI(1.46) and DBD::Pg (1.32), my
scripts started to fail while feeding arrays into stored procedures in
postgresql base (v7.4.6). The (old working) code was something like

    $dbh-> selectrow_array("SELECT dummy(?)", {}, [ 1, 2, 3]);

where 'dummy' is a function accepting array of integers and returning a single
integer:

   CREATE OR REPLACE FUNCTION dummy(INTEGER[])
   RETURNS integer AS '
   DECLARE
   BEGIN RETURN 0; END;
   ' LANGUAGE 'plpgsql' CALLED ON NULL INPUT;

Now, the scripts fail with the following error:

   DBD::Pg::st execute_array failed: ERROR: array value must start with "{" or
   dimension information

Then I've modified the script so it looks like this:

   my $sth = $dbh->prepare( "SELECT dummy(?)");
   $sth-> bind_param_array( 1, [1,2,3]);
   $sth-> execute_array({});

But the error message is still the same. Finally, I've grep'd the postgresql
source code and found the 'array value must start...' line in the array parsing
code there, and so my best guess is that there must be somethign broken in the
newer version of DBD::Pg, probably somewhere near the place the arrays are
encoded, but I'm not really sure. OTOH, the 'new' DBD::Pg is not that new,
dated 2004/02/25, so it looks that the problem is elsewhere...

Does anybody have a clue what's actually happening?

Thanks in advance,
    Dmitry Karasik

Relevant Pages

  • Re: Logon script - function array and select case not working
    ... I am trying to rewrite my logon scripts which are all in vbscript. ... set objTSout = objFSO.CreateTextFile ... objTSout.writeline retrv ... below, i am trying to have a dynamic array, becuase this is polling group member ship and everyone will be different. ...
    (microsoft.public.scripting.vbscript)
  • Re: Recordset or Array?
    ... I'd be interested in seeing the scripts. ... that GetRows returns an array of variants. ... Just because the client side recordset loads and stores all the data at once ...
    (microsoft.public.inetserver.asp.db)
  • Re: Logon script - function array and select case not working
    ... I am trying to rewrite my logon scripts which are all in vbscript. ... set objTSout = objFSO.CreateTextFile ... objTSout.writeline retrv ... below, i am trying to have a dynamic array, becuase this is polling group member ship and everyone will be different. ...
    (microsoft.public.scripting.vbscript)
  • RE: Need help with a regex
    ... I just did a quick test on my real file and it ... I definitely still have a lot to learn with both Perl and regex's, ... Such as how you initialized the array. ... Those scripts produced the output I wanted, ...
    (perl.beginners)
  • Re: threadsort
    ... >script to php. ... >Both scripts now produce the same output. ... >first string in each array of string. ... the array in the following code is sorted how I think you want ...
    (comp.lang.php)