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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
