On 10/10/07, Sam Mason <[EMAIL PROTECTED]> wrote:
>
> On Tue, Oct 09, 2007 at 10:30:15AM -0500, Erik Jones wrote:
> >On Oct 9, 2007, at 9:38 AM, Sam Mason wrote:
> >> CREATE TABLE circle ( id SERIAL PRIMARY KEY, radius REAL NOT NULL );
> >> CREATE TABLE square ( id SERIAL PRIMARY KEY, sidelen REAL NOT NULL );
> >>
> >> CREATE TABLE shapes (
> >> id SERIAL PRIMARY KEY,
> >>
> >> tag INTEGER NOT NULL,
> >>
> >> circleid INTEGER REFERENCES circle
> >> CHECK ((tag = 1) = (circleid IS NOT NULL)),
> >> squareid INTEGER REFERENCES square
> >> CHECK ((tag = 2) = (squareid IS NOT NULL))
> >> );
> >
> > You could use after triggers on your circle and shape tables to
> > automatically make the insert into shapes for you.
>
> Yes, that helps a bit with getting data in. Doing anything generally
> useful with this data once it's there is still quite painful. If you've
> ever used a language supporting something like this natively then things
> get easier. In, say, Haskell you could do:
>
> data Shape = Circle Double | Square Double
>
> if I then wanted to get the area out I'd be able to do something like:
>
> area (Circle r) = pi * r ^ 2
> area (Square l) = l ^ 2
>
> mapping this over a list is easy. In SQL I'd need to do something much
> more complicated to get the areas of all these shapes out, maybe:
>
> SELECT s.id,
> CASE WHEN s.tag = 1 THEN pi() * r ^ 2
> WHEN s.tag = 2 THEN l ^ 2 END AS area
> FROM shapes s
> LEFT JOIN circle c ON s.tag = 1 AND s.circleid = c.id
> LEFT JOIN square q ON s.tag = 2 AND s.squareid = q.id
>
> This is just a simple example, but if you've got a few of these
> structures to match up it starts to get complicated pretty quickly.
>
>
> Sam
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
I wonder if the best way to go would be to use the OO stuff.
If you had a "shapes" table, that had the various operations you were
interested in (say area), 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);
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
Ian Barber
