Search Postgresql Archives

Re: disjoint union types

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
);

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
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux