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
