Re: dynamic crosstab

2010/1/29 Pierre Chevalier <pierre(dot)chevalier1967(at)free(dot)fr>:
> Pavel Stehule claviota:
>>>
>>> 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.
>>
>
> Ok, ok. I just imagine, for now... I guess I have to swallow a big bunch of
> doc and code before I can really understand this, so I take your word.
>
>
>>> 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
>>
>
> Oh well, not tonight... ;)
>
>
>>>> 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.
>>
>
> OK, I got your point. Thanks for explaining patiently!
> In fact, what I wanted to do within postgresql, I'd better try to do it
> somewhere else, with a client more in an OLAP style.
>
> I got so used to do *everything* inside postgresql (well, through psql),
> complex queries, nested things, with visual graphs generated by queries...
> that I thought it was just endless! Oh well, this is the limit.
>

I like this strategy too. But I think - and it is important limit.
PostgreSQL is OLTP database. It isn't OLAP database or ROLAP database.
If you like do some interactive analyses (on larger dataset) - you
need minimally different server (because slow query can shots
performance), you need some specialised sw - there are lot of sw
better than postgres for this - Mondrian, Pentaho, olap4j.

>
> Now, talking about GUI clients: I've been looking for a long time for a
> decent tool that could replace an access, to interact with my postgresql
> database. So far, the best I found is knoda, for my needs. I battled a bit
> with oobase, not very convincing, or I missed something. Apparently, as I
> can google, quite a few projects have been abandoned. Does someone knwo if
> there something new, on this side?
>
> I'm also quite "nostalgique" (English?) of dBase IV, and its screens, forms
> and tables with the F4 key... I was wandering whether a project of an
> ncurses-based front-end for postgreql (actually, it could be for any FLOSS
> database, I guess) could be developed: again, does someone know if such a
> project exists?

sorry. I don't know about any

Regards
Pavel Stehule

>
> --
> Pierre Chevalier   Mesté Duran 32100 Condom
>  Tél :  09 75 27 45 62  - 06 37 80 33 64
>  http://pierremariechevalier.free.fr/
>  Logiciels Libres dans le Gers: http://gnusquetaires.org/
>
>
>
>