Re: dynamic crosstab

From: Pierre Chevalier <pierre(dot)chevalier1967(at)free(dot)fr>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: dynamic crosstab
Date: 2010-01-27 11:16:57
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: 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

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


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 :
icq# : 10432285
Logiciels Libres dans le Gers:

In response to


Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2010-01-27 11:55:35 Re: dynamic crosstab
Previous Message Yan Cheng Cheok 2010-01-27 10:54:51 Problem after installing triggering function