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.
> 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.
Thanks,
Sam
