Re: How to pass ad-hoc "complex" data structures?

On Nov 6, 2012, at 3:28 PM, Tim Landscheidt <[email protected]> wrote:

> Ah, that's a nice approach.  Basically:
> 
> | my $s = $DB->prepare ('SELECT unnest(:Data::TEXT[][]);') or die ($DB->errstr ());
> | my @Data = (['1', 'A'], ['4', 'C'], ['7', 'Z']);
> | $s->bind_param (':Data', \@Data) or die ($DB->errstr ());
> | $s->execute () or die ($DB->errstr ());
> 
> works, but flattens the structure.

Yeah, unnest does that.

>  Is using
> generate_subscripts() the only way to access the data tuple
> by tuple?

You mean element by element in the array? Yes, I think so, unless you write a PL/pgSQL function, in which case you can use `FOREACH x IN ARRAY array`.

http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY

Best,

David