2 -- Created by SQL::Translator::Producer::PostgreSQL
3 -- Created on Sun Jul 11 20:05:37 2010
8 DROP TABLE "_coffee" CASCADE;
9 CREATE TABLE "_coffee" (
11 "flavor" text DEFAULT 'good' NOT NULL,
18 DROP TABLE "_sumatra" CASCADE;
19 CREATE TABLE "_sumatra" (
20 "id" integer NOT NULL,
21 "aroma" text NOT NULL,
28 DROP TABLE "sugar" CASCADE;
29 CREATE TABLE "sugar" (
31 "sweetness" integer NOT NULL,
38 DROP VIEW IF EXISTS "coffee";
39 CREATE VIEW "coffee" ( "id", "flavor" ) AS
40 SELECT _coffee.id, flavor FROM _coffee;
42 CREATE OR REPLACE FUNCTION coffee_insert
44 RETURNS VOID AS $function$
46 INSERT INTO _coffee ( flavor) VALUES ( _flavor );
48 $function$ LANGUAGE plpgsql;
51 CREATE OR REPLACE FUNCTION coffee_update
52 (_id INTEGER, _flavor TEXT)
53 RETURNS VOID AS $function$
55 UPDATE _coffee SET flavor = _flavor WHERE ( id = _id );
57 $function$ LANGUAGE plpgsql;
60 CREATE OR REPLACE FUNCTION coffee_delete
62 RETURNS VOID AS $function$
64 DELETE FROM _coffee WHERE ( id = _id );
66 $function$ LANGUAGE plpgsql;
69 CREATE RULE _coffee_insert_rule AS
72 SELECT coffee_insert(NEW.flavor)
76 CREATE RULE _coffee_update_rule AS
79 SELECT coffee_update(OLD.id, NEW.flavor)
83 CREATE RULE _coffee_delete_rule AS
86 SELECT coffee_delete(OLD.id)
94 DROP VIEW IF EXISTS "sumatra";
95 CREATE VIEW "sumatra" ( "id", "flavor", "sweetness", "aroma" ) AS
96 SELECT _sumatra.id, aroma, sweetness, flavor FROM _sumatra _sumatra JOIN sugar sugar ON sugar.id = _sumatra.id JOIN coffee coffee ON coffee.id = _sumatra.id;
98 CREATE OR REPLACE FUNCTION sumatra_insert
99 (_aroma TEXT, _sweetness INTEGER, _flavor TEXT)
100 RETURNS VOID AS $function$
102 INSERT INTO coffee ( flavor) VALUES ( _flavor );
103 INSERT INTO _sumatra ( aroma, id) VALUES ( _aroma, currval('_coffee_id_seq') );
104 INSERT INTO sugar ( id, sweetness) VALUES ( currval('_coffee_id_seq'), _sweetness );
106 $function$ LANGUAGE plpgsql;
109 CREATE OR REPLACE FUNCTION sumatra_update
110 (_id INTEGER, _aroma TEXT, _sweetness INTEGER, _flavor TEXT)
111 RETURNS VOID AS $function$
113 UPDATE _sumatra SET aroma = _aroma WHERE ( id = _id );
114 UPDATE sugar SET sweetness = _sweetness WHERE ( id = _id );
115 UPDATE coffee SET flavor = _flavor WHERE ( id = _id );
117 $function$ LANGUAGE plpgsql;
120 CREATE OR REPLACE FUNCTION sumatra_delete
122 RETURNS VOID AS $function$
124 DELETE FROM _sumatra WHERE ( id = _id );
125 DELETE FROM sugar WHERE ( id = _id );
126 DELETE FROM coffee WHERE ( id = _id );
128 $function$ LANGUAGE plpgsql;
131 CREATE RULE _sumatra_insert_rule AS
134 SELECT sumatra_insert(NEW.aroma, NEW.sweetness, NEW.flavor)
138 CREATE RULE _sumatra_update_rule AS
141 SELECT sumatra_update(OLD.id, NEW.aroma, NEW.sweetness, NEW.flavor)
145 CREATE RULE _sumatra_delete_rule AS
148 SELECT sumatra_delete(OLD.id)