Is this doable using Postgresql crosstab or some other function?
Hi folkes,
this is my first message on this list.
I have quite a challenging problem and my own skills seem not to be
adequate for resolving it.
I have a relational model where the basic idea is to store data
vertically instead using traditional horizontal approach. So instead of
having a row in a table with n columns, I have n rows with value columns
for different data types. This makes the model very dynamic but also
difficult to use. I try to describe the (simplified) core of the model
(or at least the parts which have some meaning) in the following pseudo
definition:
Table values
ID serial pk
instanceID integer fk1 (never mind this)
parametertypeID integer fk2
value_integer integer
value_varchar character varying
value_date date
stamp timestamp
Table parameters
parametertypeID serial pk
typeid integer fk1
parameternameID integer fk2
Table parameternames
paramaternameID serial pk
parametername character varying
parameterdatatype integer or like enum(1,2,3) (this defines whether to
use value_integer, value_varchar or value_date)
From these three tables I would like to create a select statement where
the response is the following (where parameters.typeid = x and
values.timestamp = dd.mm.yyyy hh.mm.ss.xx)
instanceID integer
parameternames.parametername#1, value and datatype from value_integer,
value_varchar or value_date
parameternames.parametername#2, value and datatype from value_integer,
value_varchar or value_date
...
parameternames.parametername#n, value and datatype from value_integer,
value_varchar or value_date
Quite challanging, right?
- mika -
On 04/10/12 1:52 AM, mika@digikartta.net wrote:
Quite challanging, right?
yes.
and quite contrary to the relational data model.
whats the point of going to all this abstraction?
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On Apr 10, 2012, at 1:52, <mika@digikartta.net> wrote:
Quite challanging, right?
Aside from the possibly extremely rare case where this is actually functional the only challenging task is to tell off whomever gave you the idea/requirement that your schema is desirable - even if that person is yourself.
That aside, judicious use of CASE constructs, and probably UNION, will get you your answers. Keep in mind that you likely need to end up casting all of your values to varchar/text in the end.
You may also want to look at the HSTORE extension.
Given the generic nature of your inquiry actual specific advice is impossible to give.
David J.
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.
- mika -
On Tue, 10 Apr 2012 01:58:46 -0700, John R Pierce <pierce@hogranch.com>
wrote:
Show quoted text
On 04/10/12 1:52 AM, mika@digikartta.net wrote:
Quite challanging, right?
yes.
and quite contrary to the relational data model.
whats the point of going to all this abstraction?
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
This schema is in use in one quite big system, which utilizes different
DB than Postgresql.
It is fully functional.
- mika -
On Tue, 10 Apr 2012 02:19:14 -0700, David Johnston <polobo@yahoo.com>
wrote:
Show quoted text
On Apr 10, 2012, at 1:52, <mika@digikartta.net> wrote:
Quite challanging, right?
Aside from the possibly extremely rare case where this is actually
functional the only challenging task is to tell off whomever gave you
the idea/requirement that your schema is desirable - even if that
person is yourself.That aside, judicious use of CASE constructs, and probably UNION,
will get you your answers. Keep in mind that you likely need to end
up casting all of your values to varchar/text in the end.You may also want to look at the HSTORE extension.
Given the generic nature of your inquiry actual specific advice is
impossible to give.David J.
On Tue, Apr 10, 2012 at 4:27 AM, <mika@digikartta.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
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@hogranch.com>
wrote:
On 04/10/12 2:24 AM, mika@digikartta.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:
Show quoted text
On Tue, Apr 10, 2012 at 4:27 AM,<mika@digikartta.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
On Tue, Apr 10, 2012 at 9:31 AM, Mika M Lehtonen <mika@digikartta.net> wrote:
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?
Mostly no. A hardwired restriction is that a view has a rigidly
defined list of columns with defined types. You can skirt that
restriction a couple of ways -- for example your view could be a
single column text (or xml, or hstore) with the columns you want
encoded into it.
2) Is it possible to name view colums based on some data retrieved with
select statement?
nope -- in fact the point of views is to create a well defined
representation of something which is the opposite of what you are
trying to do.
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..
merlin
On Tue, Apr 10, 2012 at 09:43:52AM -0500, Merlin Moncure wrote:
Mostly no. A hardwired restriction is that a view has a rigidly
defined list of columns with defined types. You can skirt that
restriction a couple of ways -- for example your view could be a
single column text (or xml, or hstore) with the columns you want
encoded into it.
I'm wondering whether a set-returning (or these days, I guess,
table-returning) function or a polymorphic function might make sense
here. I haven't read the use case carefully (and I probably won't),
but it seems like it might not be impossible that way.
Best,
A
--
Andrew Sullivan
ajs@crankycanuck.ca