2010/1/27 Pierre Chevalier <pierre.chevalier1967@xxxxxxx>: > 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/ > ____________________________________________________________________________ > > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
