2 -- Created by SQL::Translator::Producer::PostgreSQL
3 -- Created on Sun Jul 11 20:05:31 2010
8 DROP TABLE "just_a_table" CASCADE;
9 CREATE TABLE "just_a_table" (
11 "name" character varying(255) NOT NULL,
18 DROP TABLE "mixin" CASCADE;
19 CREATE TABLE "mixin" (
21 "words" text NOT NULL,
28 DROP TABLE "_bar" CASCADE;
30 "id" integer NOT NULL,
34 CREATE INDEX "_bar_idx_b" on "_bar" ("b");
39 DROP TABLE "_foo" CASCADE;
45 CREATE INDEX "_foo_idx_a" on "_foo" ("a");
50 DROP VIEW IF EXISTS "bar";
51 CREATE VIEW "bar" ( "id", "a", "words", "b" ) AS
52 SELECT _bar.id, b, words, a FROM _bar _bar JOIN mixin mixin ON mixin.id = _bar.id JOIN foo foo ON foo.id = _bar.id;
54 CREATE OR REPLACE FUNCTION bar_insert
55 (_b INTEGER, _words TEXT, _a INTEGER)
56 RETURNS VOID AS $function$
58 INSERT INTO foo ( a) VALUES ( _a );
59 INSERT INTO _bar ( b, id) VALUES ( _b, currval('_foo_id_seq') );
60 INSERT INTO mixin ( id, words) VALUES ( currval('_foo_id_seq'), _words );
62 $function$ LANGUAGE plpgsql;
65 CREATE OR REPLACE FUNCTION bar_update
66 (_id INTEGER, _b INTEGER, _words TEXT, _a INTEGER)
67 RETURNS VOID AS $function$
69 UPDATE _bar SET b = _b WHERE ( id = _id );
70 UPDATE mixin SET words = _words WHERE ( id = _id );
71 UPDATE foo SET a = _a WHERE ( id = _id );
73 $function$ LANGUAGE plpgsql;
76 CREATE OR REPLACE FUNCTION bar_delete
78 RETURNS VOID AS $function$
80 DELETE FROM _bar WHERE ( id = _id );
81 DELETE FROM mixin WHERE ( id = _id );
82 DELETE FROM foo WHERE ( id = _id );
84 $function$ LANGUAGE plpgsql;
87 CREATE RULE _bar_insert_rule AS
90 SELECT bar_insert(NEW.b, NEW.words, NEW.a)
94 CREATE RULE _bar_update_rule AS
97 SELECT bar_update(OLD.id, NEW.b, NEW.words, NEW.a)
101 CREATE RULE _bar_delete_rule AS
104 SELECT bar_delete(OLD.id)
112 DROP VIEW IF EXISTS "foo";
113 CREATE VIEW "foo" ( "id", "a" ) AS
114 SELECT _foo.id, a FROM _foo;
116 CREATE OR REPLACE FUNCTION foo_insert
118 RETURNS VOID AS $function$
120 INSERT INTO _foo ( a) VALUES ( _a );
122 $function$ LANGUAGE plpgsql;
125 CREATE OR REPLACE FUNCTION foo_update
126 (_id INTEGER, _a INTEGER)
127 RETURNS VOID AS $function$
129 UPDATE _foo SET a = _a WHERE ( id = _id );
131 $function$ LANGUAGE plpgsql;
134 CREATE OR REPLACE FUNCTION foo_delete
136 RETURNS VOID AS $function$
138 DELETE FROM _foo WHERE ( id = _id );
140 $function$ LANGUAGE plpgsql;
143 CREATE RULE _foo_insert_rule AS
146 SELECT foo_insert(NEW.a)
150 CREATE RULE _foo_update_rule AS
153 SELECT foo_update(OLD.id, NEW.a)
157 CREATE RULE _foo_delete_rule AS
160 SELECT foo_delete(OLD.id)
166 -- Foreign Key Definitions
169 ALTER TABLE "_bar" ADD FOREIGN KEY ("b")
170 REFERENCES "just_a_table" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
172 ALTER TABLE "_foo" ADD FOREIGN KEY ("a")
173 REFERENCES "_bar" ("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;