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

[Greg Sabino Mullane]

On Thu, May 07, 2015 at 10:04:47PM +0200, Adam Sjøgren wrote:
 I just thought that when DBD::Pg handles simple arrays, handling
 arrays of tuples wouldn't be much further a step.

The problem isn't so much as *how* to do it as *when*. It cannot be done 
when we are reading in a resultset and come across an unknown type. It 
cannot be done at startup as the overhead of scanning the system tables 
for custom types would be too heavy. So it would need to be an invoked 
method. Then it would somehow need to be tied into the current type system. 
Right now, DBD::Pg uses the Postgres source code to generate a static 
list of core data types, and knows which ones represent arrays. There is 
no easy way to shoehorn new entries in there after the fact, so it's not 
a trivial undertaking. It may be easier/more feasible to set an attribute 
and tell DBD::Pg directly that column X needs to be handled as an array, 
but that gets messy in other ways.

If anyone wants to discuss the design further (and I do want to get this 
functionality written sometime soon), let's talk on the dbd-pg list only.

-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8


signature.asc
Description: Digital signature

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 a...@koldfront.dk

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

[Tim Bunce]

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.