2 -- $Id: regressdatabase.sql,v 1.2 2006/02/13 01:15:56 rbt Exp $
7 -- Foreign key'd structure, check constraints, primary keys
8 -- and duplicate table names in different schemas
12 ( product_id SERIAL PRIMARY KEY
13 , product_code text NOT NULL UNIQUE
14 CHECK(product_code = upper(product_code))
15 , product_description text
20 ( store_id SERIAL PRIMARY KEY
21 , store_code text NOT NULL UNIQUE
22 CHECK(store_code = upper(store_code))
23 , store_description text
26 CREATE TABLE inventory
27 ( store_id integer REFERENCES store
28 ON UPDATE CASCADE ON DELETE RESTRICT
29 , product_id integer REFERENCES product.product
30 ON UPDATE CASCADE ON DELETE RESTRICT
31 , PRIMARY KEY(store_id, product_id)
32 , quantity integer NOT NULL CHECK(quantity > 0)
36 -- Another schema with
38 CREATE SCHEMA warehouse
39 CREATE TABLE warehouse
40 ( warehouse_id SERIAL PRIMARY KEY
41 , warehouse_code text NOT NULL UNIQUE
42 CHECK(warehouse_code = upper(warehouse_code))
43 , warehouse_manager text NOT NULL
44 , warehouse_supervisor text UNIQUE
45 , warehouse_description text
46 , CHECK (upper(warehouse_manager) != upper(warehouse_supervisor))
48 CREATE TABLE inventory
49 ( warehouse_id integer REFERENCES warehouse
52 , product_id integer REFERENCES product.product
55 , PRIMARY KEY(warehouse_id, product_id)
56 , quantity integer NOT NULL
59 CREATE VIEW products AS
60 SELECT DISTINCT product.*
62 JOIN product.product USING (product_id);
65 CREATE INDEX quantity_index ON warehouse.inventory (quantity);
68 -- Simple text comments
70 --COMMENT ON DATABASE IS
71 --'This database has been created for the purpose of simple
72 -- tests on PostgreSQL Autodoc.';
74 COMMENT ON SCHEMA product IS
75 'This schema stores a list of products and information
78 COMMENT ON SCHEMA warehouse IS
79 'A list of warehouses and information on warehouses';
81 COMMENT ON TABLE warehouse.inventory IS
82 'Warehouse inventory';
84 COMMENT ON TABLE store.inventory IS
87 COMMENT ON COLUMN warehouse.warehouse.warehouse_code IS
88 'Internal code which represents warehouses for
91 COMMENT ON COLUMN warehouse.warehouse.warehouse_supervisor IS
92 'Supervisors name for a warehouse when one
93 has been assigned. The same supervisor may not
94 be assigned to more than one warehouse, per company
97 COMMENT ON COLUMN warehouse.warehouse.warehouse_manager IS
98 'Name of Warehouse Manager';
101 -- A few simple functions
103 CREATE FUNCTION product.worker(integer, integer) RETURNS integer AS
104 'SELECT $1 + $1;' LANGUAGE sql;
106 CREATE FUNCTION warehouse.worker(integer, integer) RETURNS integer AS
107 'SELECT $1 * $1;' LANGUAGE sql;
109 COMMENT ON FUNCTION product.worker(integer, integer) IS
110 'Worker function appropriate for products';
112 COMMENT ON FUNCTION warehouse.worker(integer, integer) IS
113 'Worker function appropriate for warehouses.';
115 create or replace function product.worker2 (i_1 integer, i_2 integer) returns integer as $$
121 comment on function product.worker2 (i_1 integer, i_2 integer)
122 is 'worker function that uses named parameters';
127 CREATE SCHEMA inherit
128 CREATE TABLE taba (cola integer)
129 CREATE TABLE tabb (colb integer) inherits(taba)
130 CREATE TABLE tab1 (col1 integer)
131 CREATE TABLE tab1b (col1b integer) inherits(tab1, tabb);