Re: dynamic crosstab

2010/1/27 Pierre Chevalier <pierre(dot)chevalier1967(at)free(dot)fr>:
> 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(...)));

no it isn't possible. VIEW have to have fixed numbers of columns.

You can write function that reads a cursor, create temp table, store
result and will do a copy from temp table.

There is one significant rule - any SELECT based statement have to
have known number of columns in planner time - so number of colums
must not depend on the data. There are no any workaround for it. You
can do only don't use fixed SELECT statemens (VIEWS too - it is stored
SELECT).

look on SPI interface http://www.postgresql.org/docs/8.4/interactive/spi.html

http://www.postgresql.org/docs/8.4/interactive/spi-examples.html

but you have to use cursor based interface.

Pavel

>
> 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/
> ____________________________________________________________________________
>
>
>
>