Re: dynamic crosstab

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

> in this case you need small application for reading cursor and
> transformation to CVS
>

Actually, if the small application was reading cursor, and transforming
it to a VIEW, this would solve both problems at once:
something like:

CREATE VIEW crosstabbed_thing AS
(cursor_to_dataset(SELECT do_cross_cursor(...)));

And then:
echo "COPY (SELECT * FROM crosstabbed_thing) TO stdout WITH CSV HEADER;"
| psql > youpi.csv

And there we are!
What about this plan? The cursor_to_dataset() should be written, in C if
I understand well.
I have to dig out my old C book, and browse through postgresql APIs,
code examples,etc. I guess...

A+
Pierre

--
____________________________________________________________________________
Pierre Chevalier
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
05 62 28 06 83
06 37 80 33 64
Émail : pierre.chevalier1967CHEZfree.fr
icq# : 10432285
http://pierremariechevalier.free.fr/
Logiciels Libres dans le Gers: http://gnusquetaires.org/
____________________________________________________________________________