Re: exploiting features of pg to obtain polymorphism

On Fri, 6 Oct 2006 18:12:22 -0700 (PDT)
Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:

> > Is there any good documentation, example, tutorial, pamphlet,
> > discussion... to exploit pg features to obtain "polymorphic"
> > behavior without renouncing to referential integrity?
> >
> > Inheritance seems *just* promising.
> >
> > Any methodical a approach to the problem in pg context?
>
> I don't know if this is what you are after, but is was a VERY
> interesting discussion that sounds similar to what your are looking
> for?
>
> http://archives.postgresql.org/pgsql-sql/2006-05/msg00179.php
> http://archives.postgresql.org/pgsql-general/2006-05/msg01125.php

I read it. I even kept a copy in my imap. I didn't look at it as an example of polymorphism. I'll see if I can find different uses of it.

What I was looking at was eg. list of slightly different nature but with same items.

eg.
List -> Items
List -> ListGroup -> Items

create table List (
idList integer
, otherListstuff [...]
);

create table ListGroup (
idListGroup integer
, idList integer
);

create table Items (
idItem integer
, idGeneralizedList integer
);

Each List may contain many ListGroup or many Items. I can do it... I'll do it at the cost of loosing ref. integrity.

Inheritance seems the place to look at to solve this kind of problem, but pg inheritance support is not the one I'm used to deal with in C++ for example.

1) I can't have virtual tables (or I didn't find the way to have them)
This have the side effect of "unexpected" behavior when you fill child/parent because there is no distinction between declaration and instantiation.
2) pk/pk triggers etc. aren't inherited

Anyway inheritance continue to look like a good place to start from.

I'm trying to keep all the data coherence tasks in the db.

The sql I'm writing is not "static" or in my view it is not the "final" one but rather a "definition" of the final one.
So I want to be able to define inside my sql in the most natural way my coherency requirement.

I'm already storing metadata about tables in other tables so that at db design people will be able to specify these "extra" coherency information.
At this stage the sql is actually the one that will go into the db.
These metadata are used to build up stored procedures that will take care of garbage collection for example or to generate triggers to keep data consistent.
I'm wondering if it may be a good idea to have a pre-processor to overcome the missing behavior of pg inheritance (eg. automatically create the missing pk/fk/triggers in the children, making the parent "private" so to simulate virtual parents); but it looks enough complicate to overweight the advantages of reaching the target.

So... you let me see that schema example under a new light and I'll think if I can exploit it for my tasks.
I wrote "exploiting [unnamed] features" cos I still don't know pg enough and cos I was hoping the list came up with something creative as the use of schema that maybe wouldn't come up if I explicit mention "inheritance".

I saw this too, more on the track of what I was looking for, but it wasn't inspirational as I hoped:

http://www.varlena.com/varlena/GeneralBits/98.php

--
Ivan Sergio Borgonovo
http://www.webthatworks.it