Re: dynamic crosstab - Mailing list pgsql-general

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.

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?

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

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