On 10/10/07, Sam Mason <sam@xxxxxxxxxxxxx> 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
);
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
