This is more a general programming question rather than a pg
question but maybe some postgresql features may offer a better
solution.
I'd describe the problem from an OO point of view and I'd like to
know how I could obtain a similar solution with postgresql.
If I have a hierarchy of classes, some with some proprieties some
that doesn't have them, I'd group proprieties and return them
through methods.
The base class will have all the methods returning "empty"
proprieties, the specialisations will overload those methods to
actually return the data.
A way I used to deal with this problem was to add a "has" flag to
the "parent" table and build up queries dynamically inside plpgsql:
create table parent (
parentID int primary key,
hasProp1 boolean,
name varchar(32) not null unique
);
create table child (
childID int primary key,
parentID int not null references parent
);
create table parentname_prop1 (
childID int not null references child,
somemore_Prop1
);
create or replace function GetChild(_childID int
out col1, out col2, out somemore_Prop1)
) return setof records
as
$$
declare
statement varchar(255);
begin
select into Name, hasProp1 p.hasProp1, p.name
from child c
join parent p on c.parentID=p.parentID
where c.childID=_childID;
if(hasProp1) then
statement:='select somemore_Prop1 from ' || Name || '_prop1 '
' where childID=' || _childID;
...
That's far from elegant but it looks digestible.
Once the proprieties increase in number this system start to get too
messy.
Another way would be to have a list of PropN and build up the query
dynamically checking if Name || '_' || PropN exist... but it start
to look as squeezing too much OOP out of a DB system and it makes me
think I still have to earn my DBA black belt and maybe it's time to
rewrite the schema.
I'm trying to force all this stuff in the DB rather than on the
client code since this code should be wrapped in a serializable
transaction.
I can't see any way to use postgresql own inheritance system.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
