Re: dynamic crosstab

On 1/28/2010 9:11 AM, Andy Colson wrote:
> On 1/27/2010 3:49 AM, Pierre Chevalier wrote:
>> Pavel Stehule claviota:
>>>> ...
>>>> But what I would like to do is to redirect the output of the function
>>>> (that
>>>> is, the 'result' cursor) to a view, which will be used in other
>>>> places. I
>>>> thought something like FETCH INTO would do the trick, but it doesn't.
>>>>
>>>>
>>>> Also, I need, at some point, to export the output to some CSV file. I
>>>> usually do a quick bash script as follows:
>>>>
>>>> echo "COPY (SELECT * FROM dh_litho ORDER BY id, depto) TO stdout WITH
>>>> CSV
>>>> HEADER;" | psql bdexplo > somefile.csv
>>>>
>>>> ...
>>>
>>> hmm ...it cannot work :(. You cannot forward FETCH ALL statement on
>>> server side - without programming in C
>>
>> Ach! Too bad... Oh but... I used to program in C, long time ago, on
>> HP-UX...
>>
>
>
> How do you feel about a little perl? It would be pretty simple, and
> could generate a csv based on any resultset (any number of columns). I'd
> be happy to post a little get you started code if you wanted.
>
> -Andy
>

Humm... a comma, or quotes, would have made that make sense:

> 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:

#!/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;