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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
