3 INSERT INTO config.upgrade_log (version) VALUES ('0100'); -- Scott McKellar
5 CREATE TABLE query.case_branch (
7 parent_expr INT NOT NULL REFERENCES query.expression
9 DEFERRABLE INITIALLY DEFERRED,
11 condition INT REFERENCES query.expression
12 DEFERRABLE INITIALLY DEFERRED,
13 result INT NOT NULL REFERENCES query.expression
14 DEFERRABLE INITIALLY DEFERRED,
15 CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
18 CREATE TABLE query.from_relation (
19 id SERIAL PRIMARY KEY,
20 type TEXT NOT NULL CONSTRAINT relation_type CHECK (
21 type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
24 subquery INT REFERENCES query.stored_query,
25 function_call INT REFERENCES query.expression,
26 table_alias TEXT NOT NULL,
27 parent_relation INT REFERENCES query.from_relation
29 DEFERRABLE INITIALLY DEFERRED,
30 seq_no INT NOT NULL DEFAULT 1,
31 join_type TEXT CONSTRAINT good_join_type CHECK (
32 join_type IS NULL OR join_type IN
33 ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
35 on_clause INT REFERENCES query.expression
36 DEFERRABLE INITIALLY DEFERRED,
37 CONSTRAINT join_or_core CHECK (
38 ( parent_relation IS NULL AND join_type IS NULL
39 AND on_clause IS NULL and table_alias IS NULL )
41 ( parent_relation IS NOT NULL AND join_type IS NOT NULL
42 AND on_clause IS NOT NULL )
46 CREATE UNIQUE INDEX from_parent_seq
47 ON query.from_relation( parent_relation, seq_no )
48 WHERE parent_relation IS NOT NULL;
50 -- The following foreign key had to be deferred until
51 -- query.from_relation existed
53 ALTER TABLE query.stored_query
54 ADD FOREIGN KEY (from_clause)
55 REFERENCES query.from_relation
56 DEFERRABLE INITIALLY DEFERRED;