2010/1/27 Pierre Chevalier <pierre.chevalier1967@xxxxxxx>: > Pavel Stehule claviota: >>> >>> ... >>> 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. >> > > Ach, flute... ;-( > >> You can write function that reads a cursor, create temp table, store >> result and will do a copy from temp table. >> > > Well... Not extremely elegant (it reminds me when I was stuck with access > and I could not do nested queries...), but why not? > Actually, if the table is a real temporary one (CREATE TEMPORARY TABLE), it > should not induce too much mess in the database layout. > >> 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). >> > > All right, it makes sense now... > Nut... Idea! (careful...) what about if we do, just like in a VIEW, a CREATE > OR REPLACE, systematically when we do this kind of function? The only > drawback I can think of is that we can't have anything dependant on the VIEW > we generate. no, you cannot do it. You cannot create view and same statements ask on this view on top level. if you would to understand it - you have to understand to process pipeline: parser, planner, optimizer, executor. If you understand to this stages, then you will understand what is possible and what not. > > Another idea (more danger...): what about setting a sort of flag which says > that this VIEW should *not* be included in the planner? And it will have > unexpected number of columns? Would this be *absolutely* impossible to > state? :) sorry - you can do it, but not in pg - or you have to rewrite 50% of low level code > >> 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. >> > > I'll try to Read The French Manual, rather than the one in English! I'll > look for it... > > > But the whole point is: this need of a generic cross-tab is really annoying > for a large number of people, it has been there for a long time, and I know > some people who just walk away from postgreSQL only because this feature is > lacking, and they return happily to their m$-access, therefore ignoring the > pure wealth of postgreSQL: sad, isn't it?... ms access hasn't klient-server architecture. Generating of crosstab is client side task - more - iOLTP databases are not the best tool for it. Better are OLAP databases with GUI clients - like Excel or MS Access. Regards Pavel Stehule > > 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
