Re: disjoint union types - Mailing list pgsql-general

On Oct 10, 2007, at 11:42 AM, Sam Mason wrote:

> On Wed, Oct 10, 2007 at 05:02:36PM +0100, Ian Barber wrote:
>> I wonder if the best way to go would be to use the OO stuff.
>
> I don't see how the following is object orientated, but I'm not
> sure it
> matters much.
>
>> If you had a "shapes" table, that had the various operations you were
>> interested in (say area)
>
> I prefer to keep everything as normalised as possible, if I start
> putting an area column somewhere then I'm forced to keep it updated
> somehow.  The example I gave was rather simple, but I'd like to do
> lots
> of other things beside some (simple) calculations, e.g. matching
> tables
> up depending on the internal state of each object.
>
>> , then you could have a circle table inherit from
>> that, and automatically compute the area with a trigger, as Erik
>> suggested.
>> Then you could just query shapes:
>>
>> CREATE TABLE shapes (
>>  shape_id serial PRIMARY KEY,
>>  area real not null
>> );
>>
>> CREATE TABLE circle (
>>  radius real not null
>> ) INHERITS (shapes);
>
> Postgres implements inheritance in a strange way (the way it is at the
> moment makes sense from an implementation, but not users', point of
> view), you can end up with a circle and square both with shape_id=1
> if I
> don't take a lot of care.

It doesn't take much care at all to avoid that:  don't use SERIAL for
the primary key of the parent.  Instead use an explicity "id integer
NOT NULL DEFAULT nextval('some_seq'), that way all of the child
tables will use the same sequence as the parent.  That being said,
I'm not convinced that table inheritance is what's needed here.  I'll
wait until you post the example you mention below before commenting
further.

>
>> CREATE FUNCTION circle_area() RETURNS trigger AS $circle_area$
>>     BEGIN
>>      NEW.area = pi() * NEW.radius ^ 2;
>>         RETURN NEW;
>>     END;
>> $circle_area$ LANGUAGE plpgsql;
>>
>> CREATE TRIGGER circle_area BEFORE INSERT OR UPDATE ON circle
>>     FOR EACH ROW EXECUTE PROCEDURE circle_area();
>>
>> INSERT INTO circle (radius) values (5)
>> SELECT * FROM shapes
>>
>> shape_id 1
>> area 78.5398
>
> This works to store the area of the shape, but doesn't allow me to
> work
> with work with more complicated structures.  I'll try and think up a
> better example and send it along to the list when I can describe it.



Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com