Re: dynamic crosstab

Andy Colson claviota:
> ...
> > be happy to post a little "get you started" code if you wanted.
>
> here's some code, its based on Pavel's example, and dumps csv to stdout:

Hmm, pretty cryptic to my eyes...
Thanks for not writing everything on one line!


> #!/usr/bin/perl
> use strict;
> use warnings;
> use DBI;
>
>
> my $db = DBI->connect("dbi:Pg:dbname=andy", 'andy', '', {AutoCommit =>
> 0, RaiseError => 1});
>
>
> $db->do(<<EOS);
> SELECT do_cross_cursor('shop', 'FROM shops','gender','FROM employees e
> JOIN shops s ON s.id = e.shop_id',
> 'salary')
> EOS
>
>
> my $get = $db->prepare('FETCH ALL FROM result');
> $get->execute;
>
> my $names = $get->{'NAME'};
>
> print join(',', @$names), "\n";
>
> while ( my @list = $get->fetchrow_array)
> {
>         print join(',', @list), "\n";
> }
> $get = undef;
> $db->do('commit');
> $db->disconnect;

OK, I think I got the point: instead of working from psql, you just call
the function from outside, and you walk through the resulting dataset,
adding commas and returns when needed.

I 've just tried it with my data: it works! ;)

It throws some insulting messages, though:

Use of uninitialized value $list[5] in join or string at
./crosstab_perl.pl line 24.
Use of uninitialized value $list[6] in join or string at
./crosstab_perl.pl line 24.
Use of uninitialized value $list[7] in join or string at
./crosstab_perl.pl line 24.
...

But the .csv file is there, after a redirection, and it seems fine! I'm
just worried about the messages: anything serious, or can I just ignore
them?

I'll do a diff with the csv I generated before (with psql, \a, and some
sed...)

Thanks a lot!
A+
Pierre

--
Pierre Chevalier   Mesté Duran 32100 Condom
  Tél :  09 75 27 45 62  - 06 37 80 33 64
  http://pierremariechevalier.free.fr/
  Logiciels Libres dans le Gers: http://gnusquetaires.org/