2 -- Created by SQL::Translator::Producer::PostgreSQL
3 -- Created on Sat Apr 17 08:25:41 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");
51 CREATE VIEW "foo" ( "id", "a" ) AS
52 SELECT _foo.id, a FROM _foo;
54 CREATE OR REPLACE FUNCTION foo_insert
56 RETURNS VOID AS $function$
58 INSERT INTO _foo ( a) VALUES ( _a );
60 $function$ LANGUAGE plpgsql;
63 CREATE OR REPLACE FUNCTION foo_update
64 (_id INTEGER, _a INTEGER)
65 RETURNS VOID AS $function$
67 UPDATE _foo SET a = _a WHERE ( id = _id );
69 $function$ LANGUAGE plpgsql;
72 CREATE OR REPLACE FUNCTION foo_delete
74 RETURNS VOID AS $function$
76 DELETE FROM _foo WHERE ( id = _id );
78 $function$ LANGUAGE plpgsql;
81 CREATE RULE _foo_insert_rule AS
84 SELECT foo_insert(NEW.a)
88 CREATE RULE _foo_update_rule AS
91 SELECT foo_update(OLD.id, NEW.a)
95 CREATE RULE _foo_delete_rule AS
98 SELECT foo_delete(OLD.id)
107 CREATE VIEW "bar" ( "id", "a", "words", "b" ) AS
108 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;
110 CREATE OR REPLACE FUNCTION bar_insert
111 (_b INTEGER, _words TEXT, _a INTEGER)
112 RETURNS VOID AS $function$
114 INSERT INTO foo ( a) VALUES ( _a );
115 INSERT INTO _bar ( b, id) VALUES ( _b, currval('_foo_id_seq') );
116 INSERT INTO mixin ( id, words) VALUES ( currval('_foo_id_seq'), _words );
118 $function$ LANGUAGE plpgsql;
121 CREATE OR REPLACE FUNCTION bar_update
122 (_id INTEGER, _b INTEGER, _words TEXT, _a INTEGER)
123 RETURNS VOID AS $function$
125 UPDATE _bar SET b = _b WHERE ( id = _id );
126 UPDATE mixin SET words = _words WHERE ( id = _id );
127 UPDATE foo SET a = _a WHERE ( id = _id );
129 $function$ LANGUAGE plpgsql;
132 CREATE OR REPLACE FUNCTION bar_delete
134 RETURNS VOID AS $function$
136 DELETE FROM _bar WHERE ( id = _id );
137 DELETE FROM mixin WHERE ( id = _id );
138 DELETE FROM foo WHERE ( id = _id );
140 $function$ LANGUAGE plpgsql;
143 CREATE RULE _bar_insert_rule AS
146 SELECT bar_insert(NEW.b, NEW.words, NEW.a)
150 CREATE RULE _bar_update_rule AS
153 SELECT bar_update(OLD.id, NEW.b, NEW.words, NEW.a)
157 CREATE RULE _bar_delete_rule AS
160 SELECT bar_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;