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

[email protected] ("David E. Wheeler") wrote:

>> I should have pointed out more clearer the problem :-).  The
>> query itself is fine; tmpData in this case is probably mis-
>> named as it is really meant to represent the (permanent)
>> data in PostgreSQL.

>> The question is how to pass the data structure:

>> | my @Data = ([1, 'Z'], [4, 'E'], [7, 'L']);

>> from the Perl side via DBD::Pg to use it for comparison (or
>> whatever) in PostgreSQL.

> You could pass it as a nested text array. [...]

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.  Is using
generate_subscripts() the only way to access the data tuple
by tuple?

Thanks,
Tim