Making DBD::Pg to return Postgres arrays as Perl arrays

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Making DBD::Pg to return Postgres arrays as Perl arrays

Adam Sjøgren-2
  Hi.

I have a Postgres database with some fields that are defined as arrays
in the database.

When I insert new rows where the field has a Perl-array as the value,
DBIx::Class+DBD::Pg automatically stores them in the database, as hoped
- nice!

But when I read the field again, I get the textual "Postgres-encoded"
representation back (i.e. a string like '{"(a,b,c)","(d,e,f)"}').

It looks to me like DBD::Pg can expand these to Perl arrays, given the
correct type of the field.

My question is: How do I specify this with DBIx::Class?

I have tried:

    __PACKAGE__->add_columns($field_name=>{ data_type=>'array' });
    __PACKAGE__->inflate_column($field_name=>{ inflate=>sub { ... },. deflate=>sub {...} );

in various combinations (pg_type, data_type)=>('array', 'pg_anyarray',
DBD::Pg::PG_ANYARRAY), but I seem to get the textual representation in
the inflator sub regardless.

What I would really like, is to get the array back as a Perl array.

I can do the parsing myself, but I think DBD::Pg would be better and
faster at it.

(I need the inflate_column() call, because after getting the array in, I
need to do some rearranging.)

I have browsed in the documentation of DBD::Pg and DBIx::Class, but my
search-fu is failing me.

(E.g. the cookbook mentions how to store arrays:

 * https://metacpan.org/pod/DBIx::Class::Manual::Cookbook#Working-with-PostgreSQL-array-types

but not how to read them.)

Any pointers?

  Thanks,

   Adam

--
                                                          Adam Sjøgren
                                                    [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

Will Crawford
    { data_type => "integer[]", ... }

works for me?

On 7 May 2015 at 10:17, Adam Sjøgren <[hidden email]> wrote:
  Hi.

I have a Postgres database with some fields that are defined as arrays
in the database.

When I insert new rows where the field has a Perl-array as the value,
DBIx::Class+DBD::Pg automatically stores them in the database, as hoped
- nice!

But when I read the field again, I get the textual "Postgres-encoded"
representation back (i.e. a string like '{"(a,b,c)","(d,e,f)"}').

It looks to me like DBD::Pg can expand these to Perl arrays, given the
correct type of the field.

My question is: How do I specify this with DBIx::Class?

I have tried:

    __PACKAGE__->add_columns($field_name=>{ data_type=>'array' });
    __PACKAGE__->inflate_column($field_name=>{ inflate=>sub { ... },. deflate=>sub {...} );

in various combinations (pg_type, data_type)=>('array', 'pg_anyarray',
DBD::Pg::PG_ANYARRAY), but I seem to get the textual representation in
the inflator sub regardless.

What I would really like, is to get the array back as a Perl array.

I can do the parsing myself, but I think DBD::Pg would be better and
faster at it.

(I need the inflate_column() call, because after getting the array in, I
need to do some rearranging.)

I have browsed in the documentation of DBD::Pg and DBIx::Class, but my
search-fu is failing me.

(E.g. the cookbook mentions how to store arrays:

 * https://metacpan.org/pod/DBIx::Class::Manual::Cookbook#Working-with-PostgreSQL-array-types

but not how to read them.)

Any pointers?

  Thanks,

   Adam

--
                                                          Adam Sjøgren
                                                    [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

Adam Sjøgren-2
Will writes:

>     { data_type => "integer[]", ... }

> works for me?

Ahh, cool, I will try that. My arrays are sometimes of custom types, but
I will experiment.

Thanks for the tip!


  Best regards,

    Adam

--
                                                          Adam Sjøgren
                                                    [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

Adam Sjøgren-2
Adam writes:

> Will writes:

>>     { data_type => "integer[]", ... }

>> works for me?

> Ahh, cool, I will try that. My arrays are sometimes of custom types, but
> I will experiment.

It doesn't work for me, I still get a string back. Maybe I am using too
old a DBD::Pg version (2.19.0-1), what version are you using?


  Best regards,

    Adam

--
                                                          Adam Sjøgren
                                                    [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

Adam Sjøgren-2
In reply to this post by Adam Sjøgren-2
I wrote:

> When I insert new rows where the field has a Perl-array as the value,
> DBIx::Class+DBD::Pg automatically stores them in the database, as hoped
> - nice!

> But when I read the field again, I get the textual "Postgres-encoded"
> representation back (i.e. a string like '{"(a,b,c)","(d,e,f)"}').

After looking more closely on this:

Simple arrays work out of the box, i.e. text[] I can read/write and
values are automatically converted from Perl arrays when writing to the
database, and converted back to Perl arrays when reading.

The problem I have is only when my datatype is a (custom) tuple.

I.e. in Postgres I have:

  CREATE TYPE entry AS (tag TEXT, created TIMESTAMP, notes TEXT, initials TEXT);
  ALTER TABLE vehicle ADD COLUMN entrylist entry ARRAY;

I would like to be able to do:

  $schema->resultset('Vehicle')->create({ entrylist=>[
                                                      [ 'a', '2015-05-07', 'info', 'adsj' ],
                                                      [ 'b', '2015-05-07', 'more', 'adsj' ],
                                                     ] });

and I was hoping that there was a way to configure DBIx::Class/DBD::Pg
for that to work (it doesn't, the list-ref of list-refs gets sent to the
database as '{{"a",...},{"b",...}}' which should be '{("a",...),("b",...)}',
and Postgres complains).

... and I was hoping that when reading from the database, I could
automatically get a list-ref of list-refs back.

Maybe I was hoping for too much - I just wanted to check that I wasn't
missing something obvious, before hacking together a parser for
Postgres' format.

  Best regards,

    Adam

Adding Cc to the DBD::Pg mailing list, as it now looks to me to perhaps
not just be me not configuring DBIx::Class properly.

--
                                                          Adam Sjøgren
                                                    [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

Alvar Freude
Hi,

> Am 07.05.2015 um 13:30 schrieb Adam Sjøgren <[hidden email]>:
>
> The problem I have is only when my datatype is a (custom) tuple.

The same here; I have a small sub, which decodes such strings and creates a array of hashrefs from it. It can be used to convert a pg-array which contains a combined type into a array ref of hashrefs.


Maybe this can be included in DBD::pg? But it needs Text::CSV_XS for the decoding stuff. When interested, I can post the sub.


Ciao
  Alvar


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

signature.asc (858 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

Tim Bunce
In reply to this post by Adam Sjøgren-2

On Thu, May 07, 2015 at 01:30:45PM +0200, Adam Sjøgren wrote:

> I wrote:
>
> > When I insert new rows where the field has a Perl-array as the value,
> > DBIx::Class+DBD::Pg automatically stores them in the database, as hoped
> > - nice!
>
> > But when I read the field again, I get the textual "Postgres-encoded"
> > representation back (i.e. a string like '{"(a,b,c)","(d,e,f)"}').
>
> After looking more closely on this:
>
> Simple arrays work out of the box, i.e. text[] I can read/write and
> values are automatically converted from Perl arrays when writing to the
> database, and converted back to Perl arrays when reading.
>
> The problem I have is only when my datatype is a (custom) tuple.
>
> I.e. in Postgres I have:
>
>   CREATE TYPE entry AS (tag TEXT, created TIMESTAMP, notes TEXT, initials TEXT);
>   ALTER TABLE vehicle ADD COLUMN entrylist entry ARRAY;
>
> I would like to be able to do:
>
>   $schema->resultset('Vehicle')->create({ entrylist=>[
>                                                       [ 'a', '2015-05-07', 'info', 'adsj' ],
>                                                       [ 'b', '2015-05-07', 'more', 'adsj' ],
>                                                      ] });

Take a look at https://metacpan.org/pod/PGObject::Type::Composite

Tim.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Making DBD::Pg to return Postgres arrays as Perl arrays

Adam Sjøgren
Tim writes:

>>   $schema->resultset('Vehicle')->create({ entrylist=>[
>>                                                       [ 'a', '2015-05-07', 'info', 'adsj' ],
>>                                                       [ 'b', '2015-05-07', 'more', 'adsj' ],
>>                                                      ] });

> Take a look at https://metacpan.org/pod/PGObject::Type::Composite

Yeah, Chris pointed me to that as well.

I just thought that when DBD::Pg handles "simple" arrays, handling
arrays of tuples wouldn't be much further a step.

On the write side, the above is turned into {{a,...},{b,...}}, so the
only problem is that the inner {},{} should be (),() instead.

On the read side, there might be more complications.

I haven't looked into DBD::Pg, I was just curious if I was overlooking
existing functionality.


   Thanks!

    Adam

--
 "it will turn into pointer equality or something             Adam Sjøgren
  ghastly like that"                                     [hidden email]

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Loading...