[Dbix-class] How to use arrays in database

[Lee Standen]

The problem, I think you'll find, is that the ARRAY columns supported by 
Postgres are very specific to Postgres.  I've not seen support for an 
array data type in any other database, and a quick google seems to 
confirm that it's a very Postgres specific feature.

If you need to store an array in a column, then you probably need to 
rethink your schema...


Jens Thoms Toerring wrote:
> Hello,
>
>    sorry if this is a stupid question, but I don't have much
> experience with DBIx::Class. I have a table in a database where
> some columns are arrays. I have been looking for some time for
> a way to access these arrays, idealy in a way that I can query
> or set a certain element of the array but haven't be able to
> find anything like that.
>
> After some playing around I found a way to get at the array
> as a whole by using inflate_column(), i.e. by using
>
> __PACKAGE__->inflate_column( 'array_column',
> 							 { inflate => \&inflate_string_to_array,
>                                deflate => \&deflate_array_to_string } );
>
> where the functions references point to functions that return or
> expect an array reference of the values of the array in the column:
>
> sub inflate_string_to_array {
>     $_[ 0 ] =~ s/^{(.*)}/($1)/;
>     my @arr = eval shift;
>     return \@arr;
> }
>
> and
>
> sub deflate_array_to_string {
>     return '{' . join( ',', @{ $_[ 0 ] } ) . '}';
> }
>
> Obviously, that's not a very good solution since it rather
> likely depends on the database returning a string (enclosed
> in curly braces and with comma separated values) as it does
> for PostGreSQL that I use at the moment, but that could be
> different for another one.
>
> Is there a better (database-agnostic) way to do this? And is it
> even possible to just address single elements in the array via
> one of the already available methods? Don't hesitate to point me
> to the documentation - I may simply have been too stupid to find
> the right place to look for the correct solution.
>
>                              Best regards, Jens
>