1 -- Script to create the query schema and the tables therein
5 INSERT INTO config.upgrade_log (version) VALUES ('0097'); -- Scott McKellar
7 DROP SCHEMA IF EXISTS sql CASCADE;
8 DROP SCHEMA IF EXISTS query CASCADE;
12 CREATE TABLE query.datatype (
13 id SERIAL PRIMARY KEY,
14 datatype_name TEXT NOT NULL UNIQUE,
15 is_numeric BOOL NOT NULL DEFAULT FALSE,
16 is_composite BOOL NOT NULL DEFAULT FALSE,
17 CONSTRAINT qdt_comp_not_num CHECK
18 ( is_numeric IS FALSE OR is_composite IS FALSE )
21 CREATE TABLE query.subfield (
22 id SERIAL PRIMARY KEY,
23 composite_type INT NOT NULL
24 REFERENCES query.datatype(id)
26 DEFERRABLE INITIALLY DEFERRED,
28 CONSTRAINT qsf_pos_seq_no
30 subfield_type INT NOT NULL
31 REFERENCES query.datatype(id)
32 DEFERRABLE INITIALLY DEFERRED,
33 CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
36 CREATE TABLE query.function_sig (
37 id SERIAL PRIMARY KEY,
38 function_name TEXT NOT NULL,
39 return_type INT REFERENCES query.datatype(id)
40 DEFERRABLE INITIALLY DEFERRED,
41 is_aggregate BOOL NOT NULL DEFAULT FALSE,
42 CONSTRAINT qfd_rtn_or_aggr CHECK
43 ( return_type IS NULL OR is_aggregate = FALSE )
46 CREATE INDEX query_function_sig_name_idx
47 ON query.function_sig (function_name);
49 CREATE TABLE query.function_param_def (
50 id SERIAL PRIMARY KEY,
51 function_id INT NOT NULL
52 REFERENCES query.function_sig( id )
54 DEFERRABLE INITIALLY DEFERRED,
56 CONSTRAINT qfpd_pos_seq_no CHECK
59 REFERENCES query.datatype( id )
60 DEFERRABLE INITIALLY DEFERRED,
61 CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)