Re: Is this doable using Postgresql crosstab or some other function?

Merlin,
thanks for your reply. Semidoable? Let's break this into parts:
1) Is it possible to create a view which have dynamic number of colums
based on the select response?
2) Is it possible to name view colums based on some data retrieved with
select statement?

My pseudo definition, which was already simplified, could be simlified
more. Maybe I do that and try crosstab for starters. As I told John,
this model is one that works fine in one large application using MS
SQL-Server. This view I am trying to create, is something that the
application mentioned doesn't include. The model itself has been proven
to be all working and extremely dynamical.

This is what I discussed with John outside the list, forgive me, I
didn't notice that, neither did John I guess..

- mika -

-----------------------------------------------------------------------------------------------------------------------------------------------------

John,
dynamic structures. Even if they were only one and the administrator
user changed one, it would have some challange on it.

Actually I have built my app on top of Apache Cocoon, so using XML, as
Cocoon utilizes SAX stream and everything is already in XML syntax,
wouldn't be a bad option. But this question of mine is considering a
part where the consumer is a GIS-Server which don't know anything about
XML datastores. PostGIS extension on Postgresql makes it a suitable
datastore.

- mika -

On Tue, 10 Apr 2012 02:37:39 -0700, John R Pierce <pierce(at)hogranch(dot)com>
wrote:
On 04/10/12 2:24 AM, mika(at)digikartta(dot)net wrote:
>
> Let's say I have tens or hundreds or thousands of feature (instance)
> types. Each of them would need its own table.
> Let's say I give for the application administrator, a possibility to
> create new feature types? He/she merely knows what's the database is.
> So I need a model which is capable for offering "dynamic table
> structures".

you have 1000s of different data structures (classes) in your
application? really?

it sounds to me like you're describing a key-value store. these
have no ready method of implementing data integrity, and make a very
poor fit to the relational model, resulting in very cumbersome queries
that don't execute efficiently.

you'll welcomed with open arms by the NoSQL community, however.
store everything as XML and go to town with any number of cloudy
storage systems.

10.4.2012 16:13, Merlin Moncure kirjoitti:
> On Tue, Apr 10, 2012 at 4:27 AM,<mika(at)digikartta(dot)net> wrote:
>> Let's say I have tens or hundreds or thousands of feature (instance) types.
>> Each of them would need its own table.
>> Let's say I give for the application administrator, a possibility to create
>> new feature types? He/she merely knows what's the database is.
>> So I need a model which is capable for offering "dynamic table structures".
>>
>> I already have implemented parts which allow creating these types, creating
>> instances of them and populating those instances with data. Now I have to
>> come up with a method for flattening that data into one view so that it can
>> be used directly.
> If you want a schemaless design in a relational database, you have a
> some options: EAV model, hstore, xml (soon json too) as data. I
> consider these to be niche solutions not well suited to general
> purpose data management. For the most part, SQL really only works
> properly with a well defined schema.
>
> Your incoming data looks to be EAV-ish. You can build horizontal
> structures with crosstab and what you're trying to do looks semi
> doable, but it's going to to be quite difficult.
>
> merlin