March 12th, 2011 by depesz | Tags: , , , , , , , , | 2 comments »
Did it help? If yes - maybe you can help me?

On 18th of February, Alvaro Herrera committed patch:

Convert Postgres arrays to Perl arrays on PL/perl input arguments
 
More generally, arrays are turned in Perl array references, and row and
composite types are turned into Perl hash references.  This is done
recursively, in a way that's natural to every Perl programmer.
 
To avoid a backwards compatibility hit, the string representation of
each structure is also available if the function requests it.
 
Authors: Alexey Klyukin and Alex Hunsaker.
Some code cleanups by me.

This is actually (at least for me) amazing.

Forever, when you passed any kind of data to PL/Perl, it was passed using string representation.

So, array of integers was passed as string, like ‘{1,2,3}'.

Parsing it was left to programmer, which sucked a lot. Why? Well, while it might look simple (especially when looking at int4[]), it is not when you'll consider text[], and values in array that contain spaces, quotation characters or backslash.

Example of such parser can be seen for example in my previous blog post (please note also comment from 2nd of April 2010.

Now, luckily, the problem is gone. And you can simply:

$ CREATE OR REPLACE FUNCTION test( TEXT[] ) RETURNS void as $function$
my $input = shift;
for my $value ( @{ $input } ) {
    warn( 'Value: ' . $value );
}
$function$ language plperl;

and now, you can:

$ select test( ARRAY[ E'a\\b', 'a"bc', 'a''dd', 'abc' ] );
psql:z.sql:9: WARNING:  Value: a\b at line 4.
psql:z.sql:9: WARNING:  Value: a"bc at line 4.
psql:z.sql:9: WARNING:  Value: a'dd at line 4.
psql:z.sql:9: WARNING:  Value: abc at line 4.
 test
------
 
(1 row)

What's even cooler – if you stringify passed value, it still contains what it contained in previous versions:

$ CREATE OR REPLACE FUNCTION test( TEXT[] ) RETURNS void as $function$
my $input = shift;
warn( "input = [$input]" );
$function$ language plperl;
 
$ select test( ARRAY[ E'a\\b', 'a"bc', 'a''dd', 'abc' ] );
psql:z.sql:7: WARNING:  input = [{"a\\b","a\"bc",a'dd,abc}] at line 3.

Thanks to this, your previous code, which parsed passed arrays will not break!

So, let's see if it will also work with more complex structures.

First, let's make a test function with PL/PerlU:

$ CREATE OR REPLACE FUNCTION test( text[] ) RETURNS void as $function$
my $input = shift;
use Data::Dumper;
warn(Dumper($input));
$function$ language plperlu;

sanity check:

$ select test( ARRAY[ E'a\\b', 'a"bc', 'a''dd', 'abc' ] );
psql:z.sql:8: WARNING:  $VAR1 = bless( {
                 'array' => [
                              'a\\b',
                              'a"bc',
                              'a\'dd',
                              'abc'
                            ],
                 'typeoid' => 1009
               }, 'PostgreSQL::InServer::ARRAY' );

OK, as we can see the structure is passed as array.

What about something more complex?

$ select test( array[ array[ array[ '0', '9' ], array[ 'a', 'c' ]], array[array[ 'e', 'f' ], array['q', 'w'] ]] );
psql:z.sql:8: WARNING:  $VAR1 = bless( {
                 'array' => [
                              [
                                [
                                  '0',
                                  '9'
                                ],
                                [
                                  'a',
                                  'c'
                                ]
                              ],
                              [
                                [
                                  'e',
                                  'f'
                                ],
                                [
                                  'q',
                                  'w'
                                ]
                              ]
                            ],
                 'typeoid' => 1009
               }, 'PostgreSQL::InServer::ARRAY' );

Same thing works with other datatypes, like table rows, and arrays of table rows:

$ CREATE OR REPLACE FUNCTION test( some_table[] ) RETURNS void as $function$
my $input = shift;
use Data::Dumper;
warn(Dumper($input));
$function$ language plperlu;
 
$ select test( array( select c from some_table c limit 2 ) );
psql:z.sql:8: WARNING:  $VAR1 = bless( {
                 'array' => [
                              {
                                'some_text' => 'aa',
                                'id' => '1'
                              },
                              {
                                'some_text' => 'bb',
                                'id' => '2'
                              }
                            ],
                 'typeoid' => 18632
               }, 'PostgreSQL::InServer::ARRAY' );

Now, that's really sweet thing.

  1. 2 comments

  2. Mar 13, 2011

    This is great news! It is an example of how cross-language programming should work, where parameter passing uses each language’s closest analogies, such as arrays to arrays, composites and rows to hashes, etc. Now I would hope that DBD::Pg was this complete for client-side Perl.

  3. # Martin
    Mar 14, 2011

    What does DBD::Pg not do? It handles array refs as arguments when executing SQL. When receiving data it handles arrays and booleans correctly (correctly is array ref and 0/1).

Leave a comment