Skip Menu |

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 54224
Status: open
Priority: 0/
Queue: DBD-Pg

People
Owner: Nobody in particular
Requestors: marius [...] ieval.ro
tsibley [...] cpan.org
yorhel [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: (no value)
Fixed in: (no value)



Subject: pg_expand_array doesn't work for custom data types
When fetching custom array columns from the database, the arrays are not expanded into perl arrayrefs, but are returned as strings instead: use DBI; my $db = DBI->connect(..); my $q1 = $db->prepare("SELECT '{1,2}'::int[]"); $q1->execute(); print Dumper $q1->fetchrow_array(); # nicely prints [1,2] $db->do("CREATE TYPE customint AS ENUM('1','2')"); my $q2 = $db->prepare("SELECT '{1,2}'::customint[]"); $q2->execute(); print Dumper $q2->fetchrow_array(); # prints "{1,2}", not an array (perl 5.10.1 and DBD::Pg 2.16.1)
Just confirmed this bug in 2.18.1 using a composite type: #!/usr/local/bin/perl -w use v5.14; use utf8; use DBI; my $dbh = DBI->connect( 'dbi:Pg:dbname=try', '', '', { PrintError => 0, RaiseError => 1, AutoCommit => 1, pg_enable_utf8 => 1, pg_server_prepare => 0, } ); $dbh->begin_work; $dbh->do(q{CREATE TYPE things AS (a TEXT, b INTEGER)}); my ($val) = $dbh->selectrow_array( q{SELECT ARRAY[ROW('foo', 2),ROW('bar baz', 2)]::things[]} ); say ref $val; use Data::Dump; ddx $val; $dbh->rollback; $dbh->disconnect; This outputs: # try:24: "{\"(foo,2)\",\"(\\\"bar baz\\\",2)\"}" Curiously, it seems to work fine for RECORDs. Remove the cast to ::things[] on line 21 and the output is correct: ARRAY # try:26: ["(foo,2)", "(\"bar baz\",2)"] I'm wondering if the array parser is somehow too type-dependent, like the UTF-8 recognition is. David
On Wed Aug 03 13:49:16 2011, DWHEELER wrote: Show quoted text
> Just confirmed this bug in 2.18.1 using a composite type: > ... > Curiously, it seems to work fine for RECORDs. Remove the cast to > ::things[] on line 21...
It looks like it is down to this check on 3414 of dbdimp.c strncmp(type_info->arrayout, "array", 5) With DBI_TRACE=1 and the ::things[] cast I see: Unknown type returned by Postgres: 382911089. Setting to UNKNOWN If I abuse that information to make the following horrible and wrong change to types.c, then I get an array. Or, recasting to ::text[] works too. ;-) @@ -195,6 +195,7 @@ sql_type_info_t* pg_type_data(int sql_type) case PG_POINTARRAY: return &pg_types[31]; case PG_POLYGONARRAY: return &pg_types[32]; case PG_RECORDARRAY: return &pg_types[33]; + case 382911089: return &pg_types[33];
I am open to ideas on how to solve this. Obviously, it will mean DBD::Pg querying the system catalogs for type information. But when should it do this?
This hit me today on when aggregating a column using citext into an array. Although I found a workaround, it would be great to see this fixed.
Can you provide a sample test case using citext? We could at least start writing failing tests....
On Wed Oct 01 15:36:19 2014, OSCHWALD wrote: Show quoted text
> This hit me today on when aggregating a column using citext into an > array. Although I found a workaround, it would be great to see this > fixed.
On Wed Oct 01 16:38:14 2014, TURNSTEP wrote: Show quoted text
> Can you provide a sample test case using citext? We could at least > start writing failing tests....
I'm also getting hit by this because of citext. I'm happy to write a test case for this if it'll help push support for custom array types...