diff options
Diffstat (limited to 'src/test/modules/test_ddl_deparse/sql')
21 files changed, 483 insertions, 0 deletions
diff --git a/src/test/modules/test_ddl_deparse/sql/alter_function.sql b/src/test/modules/test_ddl_deparse/sql/alter_function.sql new file mode 100644 index 0000000..45c8d1e --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/alter_function.sql @@ -0,0 +1,17 @@ +-- +-- ALTER_FUNCTION +-- + +ALTER FUNCTION plpgsql_function_trigger_1 () + SET SCHEMA foo; + +ALTER FUNCTION foo.plpgsql_function_trigger_1() + COST 10; + +CREATE ROLE regress_alter_function_role; + +ALTER FUNCTION plpgsql_function_trigger_2() + OWNER TO regress_alter_function_role; + +DROP OWNED BY regress_alter_function_role; +DROP ROLE regress_alter_function_role; diff --git a/src/test/modules/test_ddl_deparse/sql/alter_sequence.sql b/src/test/modules/test_ddl_deparse/sql/alter_sequence.sql new file mode 100644 index 0000000..9b2799f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/alter_sequence.sql @@ -0,0 +1,15 @@ +-- +-- ALTER_SEQUENCE +-- + +ALTER SEQUENCE fkey_table_seq + MINVALUE 10 + START 20 + CACHE 1 + NO CYCLE; + +ALTER SEQUENCE fkey_table_seq + RENAME TO fkey_table_seq_renamed; + +ALTER SEQUENCE fkey_table_seq_renamed + SET SCHEMA foo; diff --git a/src/test/modules/test_ddl_deparse/sql/alter_table.sql b/src/test/modules/test_ddl_deparse/sql/alter_table.sql new file mode 100644 index 0000000..dec53a0 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/alter_table.sql @@ -0,0 +1,21 @@ +CREATE TABLE parent ( + a int +); + +CREATE TABLE child () INHERITS (parent); + +CREATE TABLE grandchild () INHERITS (child); + +ALTER TABLE parent ADD COLUMN b serial; + +ALTER TABLE parent RENAME COLUMN b TO c; + +ALTER TABLE parent ADD CONSTRAINT a_pos CHECK (a > 0); + +CREATE TABLE part ( + a int +) PARTITION BY RANGE (a); + +CREATE TABLE part1 PARTITION OF part FOR VALUES FROM (1) to (100); + +ALTER TABLE part ADD PRIMARY KEY (a); diff --git a/src/test/modules/test_ddl_deparse/sql/alter_ts_config.sql b/src/test/modules/test_ddl_deparse/sql/alter_ts_config.sql new file mode 100644 index 0000000..ac13e21 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/alter_ts_config.sql @@ -0,0 +1,8 @@ +-- +-- ALTER TEXT SEARCH CONFIGURATION +-- + +CREATE TEXT SEARCH CONFIGURATION en (copy=english); + +ALTER TEXT SEARCH CONFIGURATION en + ALTER MAPPING FOR host, email, url, sfloat WITH simple; diff --git a/src/test/modules/test_ddl_deparse/sql/alter_type_enum.sql b/src/test/modules/test_ddl_deparse/sql/alter_type_enum.sql new file mode 100644 index 0000000..8999b38 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/alter_type_enum.sql @@ -0,0 +1,6 @@ +--- +--- ALTER_TYPE_ENUM +--- + +ALTER TYPE enum_test ADD VALUE 'zzz' AFTER 'baz'; +ALTER TYPE enum_test ADD VALUE 'aaa' BEFORE 'foo'; diff --git a/src/test/modules/test_ddl_deparse/sql/comment_on.sql b/src/test/modules/test_ddl_deparse/sql/comment_on.sql new file mode 100644 index 0000000..fc29a73 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/comment_on.sql @@ -0,0 +1,14 @@ +-- +-- COMMENT_ON +-- + +COMMENT ON SCHEMA foo IS 'This is schema foo'; +COMMENT ON TYPE enum_test IS 'ENUM test'; +COMMENT ON TYPE int2range IS 'RANGE test'; +COMMENT ON DOMAIN japanese_postal_code IS 'DOMAIN test'; +COMMENT ON SEQUENCE fkey_table_seq IS 'SEQUENCE test'; +COMMENT ON TABLE datatype_table IS 'This table should contain all native datatypes'; +COMMENT ON VIEW datatype_view IS 'This is a view'; +COMMENT ON FUNCTION c_function_test() IS 'FUNCTION test'; +COMMENT ON TRIGGER trigger_1 ON datatype_table IS 'TRIGGER test'; +COMMENT ON RULE rule_1 ON datatype_table IS 'RULE test'; diff --git a/src/test/modules/test_ddl_deparse/sql/create_conversion.sql b/src/test/modules/test_ddl_deparse/sql/create_conversion.sql new file mode 100644 index 0000000..813c66d --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_conversion.sql @@ -0,0 +1,6 @@ +--- +--- CREATE_CONVERSION +--- + +-- Simple test should suffice for this +CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; diff --git a/src/test/modules/test_ddl_deparse/sql/create_domain.sql b/src/test/modules/test_ddl_deparse/sql/create_domain.sql new file mode 100644 index 0000000..6ab5525 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_domain.sql @@ -0,0 +1,10 @@ +--- +--- CREATE_DOMAIN +--- +CREATE DOMAIN domainvarchar VARCHAR(5); + +CREATE DOMAIN japanese_postal_code AS TEXT +CHECK( + VALUE ~ '^\d{3}$' +OR VALUE ~ '^\d{3}-\d{4}$' +); diff --git a/src/test/modules/test_ddl_deparse/sql/create_extension.sql b/src/test/modules/test_ddl_deparse/sql/create_extension.sql new file mode 100644 index 0000000..d23e7fd --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_extension.sql @@ -0,0 +1,5 @@ +--- +--- CREATE_EXTENSION +--- + +CREATE EXTENSION pg_stat_statements; diff --git a/src/test/modules/test_ddl_deparse/sql/create_rule.sql b/src/test/modules/test_ddl_deparse/sql/create_rule.sql new file mode 100644 index 0000000..60ac151 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_rule.sql @@ -0,0 +1,31 @@ +--- +--- CREATE_RULE +--- + + +CREATE RULE rule_1 AS + ON INSERT + TO datatype_table + DO NOTHING; + +CREATE RULE rule_2 AS + ON UPDATE + TO datatype_table + DO INSERT INTO unlogged_table (id) VALUES(NEW.id); + +CREATE RULE rule_3 AS + ON DELETE + TO datatype_table + DO ALSO NOTHING; + +CREATE RULE "_RETURN" AS + ON SELECT + TO like_datatype_table + DO INSTEAD + SELECT * FROM datatype_view; + +CREATE RULE rule_3 AS + ON DELETE + TO like_datatype_table + WHERE id < 100 + DO ALSO NOTHING; diff --git a/src/test/modules/test_ddl_deparse/sql/create_schema.sql b/src/test/modules/test_ddl_deparse/sql/create_schema.sql new file mode 100644 index 0000000..f314dc2 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_schema.sql @@ -0,0 +1,17 @@ +-- +-- CREATE_SCHEMA +-- + +CREATE SCHEMA foo; + +CREATE SCHEMA IF NOT EXISTS bar; + +CREATE SCHEMA baz; + +-- Will not be created, and will not be handled by the +-- event trigger +CREATE SCHEMA IF NOT EXISTS baz; + +CREATE SCHEMA element_test + CREATE TABLE foo (id int) + CREATE VIEW bar AS SELECT * FROM foo; diff --git a/src/test/modules/test_ddl_deparse/sql/create_sequence_1.sql b/src/test/modules/test_ddl_deparse/sql/create_sequence_1.sql new file mode 100644 index 0000000..9e6743f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_sequence_1.sql @@ -0,0 +1,11 @@ +-- +-- CREATE_SEQUENCE +-- + +CREATE SEQUENCE fkey_table_seq + INCREMENT BY 1 + MINVALUE 0 + MAXVALUE 1000000 + START 10 + CACHE 10 + CYCLE; diff --git a/src/test/modules/test_ddl_deparse/sql/create_table.sql b/src/test/modules/test_ddl_deparse/sql/create_table.sql new file mode 100644 index 0000000..39cdb9d --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_table.sql @@ -0,0 +1,142 @@ +-- +-- CREATE_TABLE +-- + +-- Datatypes +CREATE TABLE datatype_table ( + id SERIAL, + id_big BIGSERIAL, + is_small SMALLSERIAL, + v_bytea BYTEA, + v_smallint SMALLINT, + v_int INT, + v_bigint BIGINT, + v_char CHAR(1), + v_varchar VARCHAR(10), + v_text TEXT, + v_bool BOOLEAN, + v_inet INET, + v_cidr CIDR, + v_macaddr MACADDR, + v_numeric NUMERIC(1,0), + v_real REAL, + v_float FLOAT(1), + v_float8 FLOAT8, + v_money MONEY, + v_tsquery TSQUERY, + v_tsvector TSVECTOR, + v_date DATE, + v_time TIME, + v_time_tz TIME WITH TIME ZONE, + v_timestamp TIMESTAMP, + v_timestamp_tz TIMESTAMP WITH TIME ZONE, + v_interval INTERVAL, + v_bit BIT, + v_bit4 BIT(4), + v_varbit VARBIT, + v_varbit4 VARBIT(4), + v_box BOX, + v_circle CIRCLE, + v_lseg LSEG, + v_path PATH, + v_point POINT, + v_polygon POLYGON, + v_json JSON, + v_xml XML, + v_uuid UUID, + v_pg_snapshot pg_snapshot, + v_enum ENUM_TEST, + v_postal_code japanese_postal_code, + v_int2range int2range, + PRIMARY KEY (id), + UNIQUE (id_big) +); + +-- Constraint definitions + +CREATE TABLE IF NOT EXISTS fkey_table ( + id INT NOT NULL DEFAULT nextval('fkey_table_seq'::REGCLASS), + datatype_id INT NOT NULL REFERENCES datatype_table(id), + big_id BIGINT NOT NULL, + sometext TEXT COLLATE "POSIX", + check_col_1 INT NOT NULL CHECK(check_col_1 < 10), + check_col_2 INT NOT NULL, + PRIMARY KEY (id), + CONSTRAINT fkey_big_id + FOREIGN KEY (big_id) + REFERENCES datatype_table(id_big), + EXCLUDE USING btree (check_col_2 WITH =) +); + +-- Typed table + +CREATE TABLE employees OF employee_type ( + PRIMARY KEY (name), + salary WITH OPTIONS DEFAULT 1000 +); + +-- Inheritance +CREATE TABLE person ( + id INT NOT NULL PRIMARY KEY, + name text, + age int4, + location point +); + +CREATE TABLE emp ( + salary int4, + manager name +) INHERITS (person); + + +CREATE TABLE student ( + gpa float8 +) INHERITS (person); + +CREATE TABLE stud_emp ( + percent int4 +) INHERITS (emp, student); + + +-- Storage parameters + +CREATE TABLE storage ( + id INT +) WITH ( + fillfactor = 10, + autovacuum_enabled = FALSE +); + +-- LIKE + +CREATE TABLE like_datatype_table ( + LIKE datatype_table + EXCLUDING ALL +); + +CREATE TABLE like_fkey_table ( + LIKE fkey_table + INCLUDING DEFAULTS + INCLUDING INDEXES + INCLUDING STORAGE +); + + +-- Volatile table types +CREATE UNLOGGED TABLE unlogged_table ( + id INT PRIMARY KEY +); + +CREATE TEMP TABLE temp_table ( + id INT PRIMARY KEY +); + +CREATE TEMP TABLE temp_table_commit_delete ( + id INT PRIMARY KEY +) +ON COMMIT DELETE ROWS; + +CREATE TEMP TABLE temp_table_commit_drop ( + id INT PRIMARY KEY +) +ON COMMIT DROP; diff --git a/src/test/modules/test_ddl_deparse/sql/create_transform.sql b/src/test/modules/test_ddl_deparse/sql/create_transform.sql new file mode 100644 index 0000000..970d89e --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_transform.sql @@ -0,0 +1,16 @@ +-- +-- CREATE_TRANSFORM +-- + +-- Create a dummy transform +-- The function FROM SQL should have internal as single argument as well +-- as return type. The function TO SQL should have as single argument +-- internal and as return argument the datatype of the transform done. +-- We choose some random built-in functions that have the right signature. +-- This won't actually be used, because the SQL function language +-- doesn't implement transforms (there would be no point). +CREATE TRANSFORM FOR int LANGUAGE SQL ( + FROM SQL WITH FUNCTION prsd_lextype(internal), + TO SQL WITH FUNCTION int4recv(internal)); + +DROP TRANSFORM FOR int LANGUAGE SQL; diff --git a/src/test/modules/test_ddl_deparse/sql/create_trigger.sql b/src/test/modules/test_ddl_deparse/sql/create_trigger.sql new file mode 100644 index 0000000..fc0aef7 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_trigger.sql @@ -0,0 +1,18 @@ +--- +--- CREATE_TRIGGER +--- + +CREATE FUNCTION plpgsql_function_trigger_1() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +BEGIN + RETURN NEW; +END; +$$; + +CREATE TRIGGER trigger_1 + BEFORE INSERT OR UPDATE + ON datatype_table + FOR EACH ROW + EXECUTE PROCEDURE plpgsql_function_trigger_1(); diff --git a/src/test/modules/test_ddl_deparse/sql/create_type.sql b/src/test/modules/test_ddl_deparse/sql/create_type.sql new file mode 100644 index 0000000..a387cfd --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_type.sql @@ -0,0 +1,21 @@ +--- +--- CREATE_TYPE +--- + +CREATE FUNCTION text_w_default_in(cstring) + RETURNS text_w_default + AS 'textin' + LANGUAGE internal STABLE STRICT; + +CREATE FUNCTION text_w_default_out(text_w_default) + RETURNS cstring + AS 'textout' + LANGUAGE internal STABLE STRICT ; + +CREATE TYPE employee_type AS (name TEXT, salary NUMERIC); + +CREATE TYPE enum_test AS ENUM ('foo', 'bar', 'baz'); + +CREATE TYPE int2range AS RANGE ( + SUBTYPE = int2 +); diff --git a/src/test/modules/test_ddl_deparse/sql/create_view.sql b/src/test/modules/test_ddl_deparse/sql/create_view.sql new file mode 100644 index 0000000..030b76f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/create_view.sql @@ -0,0 +1,17 @@ +-- +-- CREATE_VIEW +-- + +CREATE VIEW static_view AS + SELECT 'foo'::TEXT AS col; + +CREATE OR REPLACE VIEW static_view AS + SELECT 'bar'::TEXT AS col; + +CREATE VIEW datatype_view AS + SELECT * FROM datatype_table; + +CREATE RECURSIVE VIEW nums_1_100 (n) AS + VALUES (1) +UNION ALL + SELECT n+1 FROM nums_1_100 WHERE n < 100; diff --git a/src/test/modules/test_ddl_deparse/sql/defprivs.sql b/src/test/modules/test_ddl_deparse/sql/defprivs.sql new file mode 100644 index 0000000..a0fb4c2 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/defprivs.sql @@ -0,0 +1,6 @@ +-- +-- ALTER DEFAULT PRIVILEGES +-- + +ALTER DEFAULT PRIVILEGES IN SCHEMA public + REVOKE ALL PRIVILEGES ON TABLES FROM public; diff --git a/src/test/modules/test_ddl_deparse/sql/matviews.sql b/src/test/modules/test_ddl_deparse/sql/matviews.sql new file mode 100644 index 0000000..6e22c52 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/matviews.sql @@ -0,0 +1,8 @@ +-- +-- Materialized views +-- + +CREATE MATERIALIZED VIEW ddl_deparse_mv AS + SELECT * FROM datatype_table LIMIT 1 WITH NO DATA; + +REFRESH MATERIALIZED VIEW ddl_deparse_mv; diff --git a/src/test/modules/test_ddl_deparse/sql/opfamily.sql b/src/test/modules/test_ddl_deparse/sql/opfamily.sql new file mode 100644 index 0000000..b2bacbb --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/opfamily.sql @@ -0,0 +1,52 @@ +-- copied from equivclass.sql +create type int8alias1; +create function int8alias1in(cstring) returns int8alias1 + strict immutable language internal as 'int8in'; +create function int8alias1out(int8alias1) returns cstring + strict immutable language internal as 'int8out'; +create type int8alias1 ( + input = int8alias1in, + output = int8alias1out, + like = int8 +); + +create type int8alias2; +create function int8alias2in(cstring) returns int8alias2 + strict immutable language internal as 'int8in'; +create function int8alias2out(int8alias2) returns cstring + strict immutable language internal as 'int8out'; +create type int8alias2 ( + input = int8alias2in, + output = int8alias2out, + like = int8 +); + +create cast (int8 as int8alias1) without function; +create cast (int8 as int8alias2) without function; +create cast (int8alias1 as int8) without function; +create cast (int8alias2 as int8) without function; + +create function int8alias1eq(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias1eq, + leftarg = int8alias1, rightarg = int8alias1, + commutator = =, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8alias1, int8alias1); + + +-- copied from alter_table.sql +create type ctype as (f1 int, f2 text); + +create function same(ctype, ctype) returns boolean language sql +as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2'; + +create operator =(procedure = same, leftarg = ctype, rightarg = ctype); + +create operator class ctype_hash_ops + default for type ctype using hash as + operator 1 =(ctype, ctype); diff --git a/src/test/modules/test_ddl_deparse/sql/test_ddl_deparse.sql b/src/test/modules/test_ddl_deparse/sql/test_ddl_deparse.sql new file mode 100644 index 0000000..e257a21 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/test_ddl_deparse.sql @@ -0,0 +1,42 @@ +CREATE EXTENSION test_ddl_deparse; + +CREATE OR REPLACE FUNCTION test_ddl_deparse() + RETURNS event_trigger LANGUAGE plpgsql AS +$$ +DECLARE + r record; + r2 record; + cmdtype text; + objtype text; + tag text; +BEGIN + FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() + LOOP + -- verify that tags match + tag = public.get_command_tag(r.command); + IF tag <> r.command_tag THEN + RAISE NOTICE 'tag % doesn''t match %', tag, r.command_tag; + END IF; + + -- log the operation + cmdtype = public.get_command_type(r.command); + IF cmdtype <> 'grant' THEN + RAISE NOTICE 'DDL test: type %, tag %', cmdtype, tag; + ELSE + RAISE NOTICE 'DDL test: type %, object type %', cmdtype, r.object_type; + END IF; + + -- if alter table, log more + IF cmdtype = 'alter table' THEN + FOR r2 IN SELECT * + FROM unnest(public.get_altertable_subcmdtypes(r.command)) + LOOP + RAISE NOTICE ' subcommand: %', r2.unnest; + END LOOP; + END IF; + END LOOP; +END; +$$; + +CREATE EVENT TRIGGER test_ddl_deparse +ON ddl_command_end EXECUTE PROCEDURE test_ddl_deparse(); |