Re: dynamic crosstab

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