diff options
Diffstat (limited to 'src/test/modules/test_ddl_deparse')
51 files changed, 1395 insertions, 0 deletions
diff --git a/src/test/modules/test_ddl_deparse/.gitignore b/src/test/modules/test_ddl_deparse/.gitignore new file mode 100644 index 0000000..5dcb3ff --- /dev/null +++ b/src/test/modules/test_ddl_deparse/.gitignore @@ -0,0 +1,4 @@ +# Generated subdirectories +/log/ +/results/ +/tmp_check/ diff --git a/src/test/modules/test_ddl_deparse/Makefile b/src/test/modules/test_ddl_deparse/Makefile new file mode 100644 index 0000000..3a57a95 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/Makefile @@ -0,0 +1,43 @@ +# src/test/modules/test_ddl_deparse/Makefile + +MODULES = test_ddl_deparse +PGFILEDESC = "test_ddl_deparse - regression testing for DDL deparsing" + +EXTENSION = test_ddl_deparse +DATA = test_ddl_deparse--1.0.sql + +# test_ddl_deparse must be first +REGRESS = test_ddl_deparse \ + create_extension \ + create_schema \ + create_type \ + create_conversion \ + create_domain \ + create_sequence_1 \ + create_table \ + create_transform \ + alter_table \ + create_view \ + create_trigger \ + create_rule \ + comment_on \ + alter_function \ + alter_sequence \ + alter_ts_config \ + alter_type_enum \ + opfamily \ + defprivs \ + matviews + +EXTRA_INSTALL = contrib/pg_stat_statements + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = src/test/modules/test_ddl_deparse +top_builddir = ../../../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/src/test/modules/test_ddl_deparse/README b/src/test/modules/test_ddl_deparse/README new file mode 100644 index 0000000..b12a129 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/README @@ -0,0 +1,8 @@ +test_ddl_deparse is an example of how to use the pg_ddl_command datatype. +It is not intended to do anything useful on its own; rather, it is a +demonstration of how to use the datatype, and to provide some unit tests for +it. + +The functions in this extension are intended to be able to process some +part of the struct and produce some readable output, preferably handling +all possible cases so that SQL test code can be written. diff --git a/src/test/modules/test_ddl_deparse/expected/alter_extension.out b/src/test/modules/test_ddl_deparse/expected/alter_extension.out new file mode 100644 index 0000000..e69de29 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/alter_extension.out diff --git a/src/test/modules/test_ddl_deparse/expected/alter_function.out b/src/test/modules/test_ddl_deparse/expected/alter_function.out new file mode 100644 index 0000000..69a3742 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/alter_function.out @@ -0,0 +1,15 @@ +-- +-- ALTER_FUNCTION +-- +ALTER FUNCTION plpgsql_function_trigger_1 () + SET SCHEMA foo; +NOTICE: DDL test: type simple, tag ALTER FUNCTION +ALTER FUNCTION foo.plpgsql_function_trigger_1() + COST 10; +NOTICE: DDL test: type simple, tag ALTER FUNCTION +CREATE ROLE regress_alter_function_role; +ALTER FUNCTION plpgsql_function_trigger_2() + OWNER TO regress_alter_function_role; +ERROR: function plpgsql_function_trigger_2() does not exist +DROP OWNED BY regress_alter_function_role; +DROP ROLE regress_alter_function_role; diff --git a/src/test/modules/test_ddl_deparse/expected/alter_sequence.out b/src/test/modules/test_ddl_deparse/expected/alter_sequence.out new file mode 100644 index 0000000..319f36f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/alter_sequence.out @@ -0,0 +1,15 @@ +-- +-- ALTER_SEQUENCE +-- +ALTER SEQUENCE fkey_table_seq + MINVALUE 10 + START 20 + CACHE 1 + NO CYCLE; +NOTICE: DDL test: type simple, tag ALTER SEQUENCE +ALTER SEQUENCE fkey_table_seq + RENAME TO fkey_table_seq_renamed; +NOTICE: DDL test: type simple, tag ALTER SEQUENCE +ALTER SEQUENCE fkey_table_seq_renamed + SET SCHEMA foo; +NOTICE: DDL test: type simple, tag ALTER SEQUENCE diff --git a/src/test/modules/test_ddl_deparse/expected/alter_table.out b/src/test/modules/test_ddl_deparse/expected/alter_table.out new file mode 100644 index 0000000..141060f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/alter_table.out @@ -0,0 +1,29 @@ +CREATE TABLE parent ( + a int +); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE TABLE child () INHERITS (parent); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE TABLE grandchild () INHERITS (child); +NOTICE: DDL test: type simple, tag CREATE TABLE +ALTER TABLE parent ADD COLUMN b serial; +NOTICE: DDL test: type simple, tag CREATE SEQUENCE +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: ADD COLUMN (and recurse) +NOTICE: DDL test: type simple, tag ALTER SEQUENCE +ALTER TABLE parent RENAME COLUMN b TO c; +NOTICE: DDL test: type simple, tag ALTER TABLE +ALTER TABLE parent ADD CONSTRAINT a_pos CHECK (a > 0); +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: ADD CONSTRAINT (and recurse) +CREATE TABLE part ( + a int +) PARTITION BY RANGE (a); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE TABLE part1 PARTITION OF part FOR VALUES FROM (1) to (100); +NOTICE: DDL test: type simple, tag CREATE TABLE +ALTER TABLE part ADD PRIMARY KEY (a); +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: SET NOT NULL +NOTICE: subcommand: SET NOT NULL +NOTICE: subcommand: ADD INDEX diff --git a/src/test/modules/test_ddl_deparse/expected/alter_ts_config.out b/src/test/modules/test_ddl_deparse/expected/alter_ts_config.out new file mode 100644 index 0000000..afc352f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/alter_ts_config.out @@ -0,0 +1,8 @@ +-- +-- ALTER TEXT SEARCH CONFIGURATION +-- +CREATE TEXT SEARCH CONFIGURATION en (copy=english); +NOTICE: DDL test: type simple, tag CREATE TEXT SEARCH CONFIGURATION +ALTER TEXT SEARCH CONFIGURATION en + ALTER MAPPING FOR host, email, url, sfloat WITH simple; +NOTICE: DDL test: type alter text search configuration, tag ALTER TEXT SEARCH CONFIGURATION diff --git a/src/test/modules/test_ddl_deparse/expected/alter_type_enum.out b/src/test/modules/test_ddl_deparse/expected/alter_type_enum.out new file mode 100644 index 0000000..74107c2 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/alter_type_enum.out @@ -0,0 +1,7 @@ +--- +--- ALTER_TYPE_ENUM +--- +ALTER TYPE enum_test ADD VALUE 'zzz' AFTER 'baz'; +NOTICE: DDL test: type simple, tag ALTER TYPE +ALTER TYPE enum_test ADD VALUE 'aaa' BEFORE 'foo'; +NOTICE: DDL test: type simple, tag ALTER TYPE diff --git a/src/test/modules/test_ddl_deparse/expected/comment_on.out b/src/test/modules/test_ddl_deparse/expected/comment_on.out new file mode 100644 index 0000000..129eff9 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/comment_on.out @@ -0,0 +1,23 @@ +-- +-- COMMENT_ON +-- +COMMENT ON SCHEMA foo IS 'This is schema foo'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON TYPE enum_test IS 'ENUM test'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON TYPE int2range IS 'RANGE test'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON DOMAIN japanese_postal_code IS 'DOMAIN test'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON SEQUENCE fkey_table_seq IS 'SEQUENCE test'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON TABLE datatype_table IS 'This table should contain all native datatypes'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON VIEW datatype_view IS 'This is a view'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON FUNCTION c_function_test() IS 'FUNCTION test'; +ERROR: function c_function_test() does not exist +COMMENT ON TRIGGER trigger_1 ON datatype_table IS 'TRIGGER test'; +NOTICE: DDL test: type simple, tag COMMENT +COMMENT ON RULE rule_1 ON datatype_table IS 'RULE test'; +NOTICE: DDL test: type simple, tag COMMENT diff --git a/src/test/modules/test_ddl_deparse/expected/create_conversion.out b/src/test/modules/test_ddl_deparse/expected/create_conversion.out new file mode 100644 index 0000000..e8697cf --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_conversion.out @@ -0,0 +1,6 @@ +--- +--- CREATE_CONVERSION +--- +-- Simple test should suffice for this +CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8; +NOTICE: DDL test: type simple, tag CREATE CONVERSION diff --git a/src/test/modules/test_ddl_deparse/expected/create_domain.out b/src/test/modules/test_ddl_deparse/expected/create_domain.out new file mode 100644 index 0000000..2e7f585 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_domain.out @@ -0,0 +1,11 @@ +--- +--- CREATE_DOMAIN +--- +CREATE DOMAIN domainvarchar VARCHAR(5); +NOTICE: DDL test: type simple, tag CREATE DOMAIN +CREATE DOMAIN japanese_postal_code AS TEXT +CHECK( + VALUE ~ '^\d{3}$' +OR VALUE ~ '^\d{3}-\d{4}$' +); +NOTICE: DDL test: type simple, tag CREATE DOMAIN diff --git a/src/test/modules/test_ddl_deparse/expected/create_extension.out b/src/test/modules/test_ddl_deparse/expected/create_extension.out new file mode 100644 index 0000000..4042e02 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_extension.out @@ -0,0 +1,5 @@ +--- +--- CREATE_EXTENSION +--- +CREATE EXTENSION pg_stat_statements; +NOTICE: DDL test: type simple, tag CREATE EXTENSION diff --git a/src/test/modules/test_ddl_deparse/expected/create_function.out b/src/test/modules/test_ddl_deparse/expected/create_function.out new file mode 100644 index 0000000..e69de29 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_function.out diff --git a/src/test/modules/test_ddl_deparse/expected/create_operator.out b/src/test/modules/test_ddl_deparse/expected/create_operator.out new file mode 100644 index 0000000..e69de29 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_operator.out diff --git a/src/test/modules/test_ddl_deparse/expected/create_rule.out b/src/test/modules/test_ddl_deparse/expected/create_rule.out new file mode 100644 index 0000000..fe3d047 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_rule.out @@ -0,0 +1,30 @@ +--- +--- CREATE_RULE +--- +CREATE RULE rule_1 AS + ON INSERT + TO datatype_table + DO NOTHING; +NOTICE: DDL test: type simple, tag CREATE RULE +CREATE RULE rule_2 AS + ON UPDATE + TO datatype_table + DO INSERT INTO unlogged_table (id) VALUES(NEW.id); +NOTICE: DDL test: type simple, tag CREATE RULE +CREATE RULE rule_3 AS + ON DELETE + TO datatype_table + DO ALSO NOTHING; +NOTICE: DDL test: type simple, tag CREATE RULE +CREATE RULE "_RETURN" AS + ON SELECT + TO like_datatype_table + DO INSTEAD + SELECT * FROM datatype_view; +NOTICE: DDL test: type simple, tag CREATE RULE +CREATE RULE rule_3 AS + ON DELETE + TO like_datatype_table + WHERE id < 100 + DO ALSO NOTHING; +NOTICE: DDL test: type simple, tag CREATE RULE diff --git a/src/test/modules/test_ddl_deparse/expected/create_schema.out b/src/test/modules/test_ddl_deparse/expected/create_schema.out new file mode 100644 index 0000000..8ab4eb0 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_schema.out @@ -0,0 +1,19 @@ +-- +-- CREATE_SCHEMA +-- +CREATE SCHEMA foo; +NOTICE: DDL test: type simple, tag CREATE SCHEMA +CREATE SCHEMA IF NOT EXISTS bar; +NOTICE: DDL test: type simple, tag CREATE SCHEMA +CREATE SCHEMA baz; +NOTICE: DDL test: type simple, tag CREATE SCHEMA +-- Will not be created, and will not be handled by the +-- event trigger +CREATE SCHEMA IF NOT EXISTS baz; +NOTICE: schema "baz" already exists, skipping +CREATE SCHEMA element_test + CREATE TABLE foo (id int) + CREATE VIEW bar AS SELECT * FROM foo; +NOTICE: DDL test: type simple, tag CREATE SCHEMA +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE VIEW diff --git a/src/test/modules/test_ddl_deparse/expected/create_sequence_1.out b/src/test/modules/test_ddl_deparse/expected/create_sequence_1.out new file mode 100644 index 0000000..5837ea4 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_sequence_1.out @@ -0,0 +1,11 @@ +-- +-- CREATE_SEQUENCE +-- +CREATE SEQUENCE fkey_table_seq + INCREMENT BY 1 + MINVALUE 0 + MAXVALUE 1000000 + START 10 + CACHE 10 + CYCLE; +NOTICE: DDL test: type simple, tag CREATE SEQUENCE diff --git a/src/test/modules/test_ddl_deparse/expected/create_table.out b/src/test/modules/test_ddl_deparse/expected/create_table.out new file mode 100644 index 0000000..0f2a2c1 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_table.out @@ -0,0 +1,164 @@ +-- +-- 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) +); +NOTICE: DDL test: type simple, tag CREATE SEQUENCE +NOTICE: DDL test: type simple, tag CREATE SEQUENCE +NOTICE: DDL test: type simple, tag CREATE SEQUENCE +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +NOTICE: DDL test: type simple, tag CREATE INDEX +NOTICE: DDL test: type simple, tag ALTER SEQUENCE +NOTICE: DDL test: type simple, tag ALTER SEQUENCE +NOTICE: DDL test: type simple, tag ALTER SEQUENCE +-- 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 =) +); +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +NOTICE: DDL test: type simple, tag CREATE INDEX +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: ADD CONSTRAINT (and recurse) +NOTICE: subcommand: ADD CONSTRAINT (and recurse) +-- Typed table +CREATE TABLE employees OF employee_type ( + PRIMARY KEY (name), + salary WITH OPTIONS DEFAULT 1000 +); +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: SET NOT NULL +NOTICE: DDL test: type simple, tag CREATE INDEX +-- Inheritance +CREATE TABLE person ( + id INT NOT NULL PRIMARY KEY, + name text, + age int4, + location point +); +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +CREATE TABLE emp ( + salary int4, + manager name +) INHERITS (person); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE TABLE student ( + gpa float8 +) INHERITS (person); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE TABLE stud_emp ( + percent int4 +) INHERITS (emp, student); +NOTICE: merging multiple inherited definitions of column "id" +NOTICE: merging multiple inherited definitions of column "name" +NOTICE: merging multiple inherited definitions of column "age" +NOTICE: merging multiple inherited definitions of column "location" +NOTICE: DDL test: type simple, tag CREATE TABLE +-- Storage parameters +CREATE TABLE storage ( + id INT +) WITH ( + fillfactor = 10, + autovacuum_enabled = FALSE +); +NOTICE: DDL test: type simple, tag CREATE TABLE +-- LIKE +CREATE TABLE like_datatype_table ( + LIKE datatype_table + EXCLUDING ALL +); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE TABLE like_fkey_table ( + LIKE fkey_table + INCLUDING DEFAULTS + INCLUDING INDEXES + INCLUDING STORAGE +); +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type alter table, tag ALTER TABLE +NOTICE: subcommand: ALTER COLUMN SET DEFAULT (precooked) +NOTICE: DDL test: type simple, tag CREATE INDEX +NOTICE: DDL test: type simple, tag CREATE INDEX +-- Volatile table types +CREATE UNLOGGED TABLE unlogged_table ( + id INT PRIMARY KEY +); +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +CREATE TEMP TABLE temp_table ( + id INT PRIMARY KEY +); +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +CREATE TEMP TABLE temp_table_commit_delete ( + id INT PRIMARY KEY +) +ON COMMIT DELETE ROWS; +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX +CREATE TEMP TABLE temp_table_commit_drop ( + id INT PRIMARY KEY +) +ON COMMIT DROP; +NOTICE: DDL test: type simple, tag CREATE TABLE +NOTICE: DDL test: type simple, tag CREATE INDEX diff --git a/src/test/modules/test_ddl_deparse/expected/create_transform.out b/src/test/modules/test_ddl_deparse/expected/create_transform.out new file mode 100644 index 0000000..5066051 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_transform.out @@ -0,0 +1,15 @@ +-- +-- 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)); +NOTICE: DDL test: type simple, tag CREATE TRANSFORM +DROP TRANSFORM FOR int LANGUAGE SQL; diff --git a/src/test/modules/test_ddl_deparse/expected/create_trigger.out b/src/test/modules/test_ddl_deparse/expected/create_trigger.out new file mode 100644 index 0000000..c89c847 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_trigger.out @@ -0,0 +1,18 @@ +--- +--- CREATE_TRIGGER +--- +CREATE FUNCTION plpgsql_function_trigger_1() + RETURNS TRIGGER + LANGUAGE plpgsql +AS $$ +BEGIN + RETURN NEW; +END; +$$; +NOTICE: DDL test: type simple, tag CREATE FUNCTION +CREATE TRIGGER trigger_1 + BEFORE INSERT OR UPDATE + ON datatype_table + FOR EACH ROW + EXECUTE PROCEDURE plpgsql_function_trigger_1(); +NOTICE: DDL test: type simple, tag CREATE TRIGGER diff --git a/src/test/modules/test_ddl_deparse/expected/create_type.out b/src/test/modules/test_ddl_deparse/expected/create_type.out new file mode 100644 index 0000000..dadbc8f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_type.out @@ -0,0 +1,24 @@ +--- +--- CREATE_TYPE +--- +CREATE FUNCTION text_w_default_in(cstring) + RETURNS text_w_default + AS 'textin' + LANGUAGE internal STABLE STRICT; +NOTICE: type "text_w_default" is not yet defined +DETAIL: Creating a shell type definition. +NOTICE: DDL test: type simple, tag CREATE FUNCTION +CREATE FUNCTION text_w_default_out(text_w_default) + RETURNS cstring + AS 'textout' + LANGUAGE internal STABLE STRICT ; +NOTICE: argument type text_w_default is only a shell +NOTICE: DDL test: type simple, tag CREATE FUNCTION +CREATE TYPE employee_type AS (name TEXT, salary NUMERIC); +NOTICE: DDL test: type simple, tag CREATE TYPE +CREATE TYPE enum_test AS ENUM ('foo', 'bar', 'baz'); +NOTICE: DDL test: type simple, tag CREATE TYPE +CREATE TYPE int2range AS RANGE ( + SUBTYPE = int2 +); +NOTICE: DDL test: type simple, tag CREATE TYPE diff --git a/src/test/modules/test_ddl_deparse/expected/create_view.out b/src/test/modules/test_ddl_deparse/expected/create_view.out new file mode 100644 index 0000000..2ae4e2d --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/create_view.out @@ -0,0 +1,19 @@ +-- +-- CREATE_VIEW +-- +CREATE VIEW static_view AS + SELECT 'foo'::TEXT AS col; +NOTICE: DDL test: type simple, tag CREATE VIEW +CREATE OR REPLACE VIEW static_view AS + SELECT 'bar'::TEXT AS col; +NOTICE: DDL test: type simple, tag CREATE VIEW +NOTICE: DDL test: type alter table, tag CREATE VIEW +NOTICE: subcommand: REPLACE RELOPTIONS +CREATE VIEW datatype_view AS + SELECT * FROM datatype_table; +NOTICE: DDL test: type simple, tag CREATE VIEW +CREATE RECURSIVE VIEW nums_1_100 (n) AS + VALUES (1) +UNION ALL + SELECT n+1 FROM nums_1_100 WHERE n < 100; +NOTICE: DDL test: type simple, tag CREATE VIEW diff --git a/src/test/modules/test_ddl_deparse/expected/defprivs.out b/src/test/modules/test_ddl_deparse/expected/defprivs.out new file mode 100644 index 0000000..66b2680 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/defprivs.out @@ -0,0 +1,6 @@ +-- +-- ALTER DEFAULT PRIVILEGES +-- +ALTER DEFAULT PRIVILEGES IN SCHEMA public + REVOKE ALL PRIVILEGES ON TABLES FROM public; +NOTICE: DDL test: type alter default privileges, tag ALTER DEFAULT PRIVILEGES diff --git a/src/test/modules/test_ddl_deparse/expected/matviews.out b/src/test/modules/test_ddl_deparse/expected/matviews.out new file mode 100644 index 0000000..69a5627 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/matviews.out @@ -0,0 +1,8 @@ +-- +-- Materialized views +-- +CREATE MATERIALIZED VIEW ddl_deparse_mv AS + SELECT * FROM datatype_table LIMIT 1 WITH NO DATA; +NOTICE: DDL test: type simple, tag CREATE MATERIALIZED VIEW +REFRESH MATERIALIZED VIEW ddl_deparse_mv; +NOTICE: DDL test: type simple, tag REFRESH MATERIALIZED VIEW diff --git a/src/test/modules/test_ddl_deparse/expected/opfamily.out b/src/test/modules/test_ddl_deparse/expected/opfamily.out new file mode 100644 index 0000000..c7e3a23 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/opfamily.out @@ -0,0 +1,68 @@ +-- copied from equivclass.sql +create type int8alias1; +NOTICE: DDL test: type simple, tag CREATE TYPE +create function int8alias1in(cstring) returns int8alias1 + strict immutable language internal as 'int8in'; +NOTICE: return type int8alias1 is only a shell +NOTICE: DDL test: type simple, tag CREATE FUNCTION +create function int8alias1out(int8alias1) returns cstring + strict immutable language internal as 'int8out'; +NOTICE: argument type int8alias1 is only a shell +NOTICE: DDL test: type simple, tag CREATE FUNCTION +create type int8alias1 ( + input = int8alias1in, + output = int8alias1out, + like = int8 +); +NOTICE: DDL test: type simple, tag CREATE TYPE +create type int8alias2; +NOTICE: DDL test: type simple, tag CREATE TYPE +create function int8alias2in(cstring) returns int8alias2 + strict immutable language internal as 'int8in'; +NOTICE: return type int8alias2 is only a shell +NOTICE: DDL test: type simple, tag CREATE FUNCTION +create function int8alias2out(int8alias2) returns cstring + strict immutable language internal as 'int8out'; +NOTICE: argument type int8alias2 is only a shell +NOTICE: DDL test: type simple, tag CREATE FUNCTION +create type int8alias2 ( + input = int8alias2in, + output = int8alias2out, + like = int8 +); +NOTICE: DDL test: type simple, tag CREATE TYPE +create cast (int8 as int8alias1) without function; +NOTICE: DDL test: type simple, tag CREATE CAST +create cast (int8 as int8alias2) without function; +NOTICE: DDL test: type simple, tag CREATE CAST +create cast (int8alias1 as int8) without function; +NOTICE: DDL test: type simple, tag CREATE CAST +create cast (int8alias2 as int8) without function; +NOTICE: DDL test: type simple, tag CREATE CAST +create function int8alias1eq(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8eq'; +NOTICE: DDL test: type simple, tag CREATE FUNCTION +create operator = ( + procedure = int8alias1eq, + leftarg = int8alias1, rightarg = int8alias1, + commutator = =, + restrict = eqsel, join = eqjoinsel, + merges +); +NOTICE: DDL test: type simple, tag CREATE OPERATOR +alter operator family integer_ops using btree add + operator 3 = (int8alias1, int8alias1); +NOTICE: DDL test: type alter operator family, tag ALTER OPERATOR FAMILY +-- copied from alter_table.sql +create type ctype as (f1 int, f2 text); +NOTICE: DDL test: type simple, tag CREATE TYPE +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'; +NOTICE: DDL test: type simple, tag CREATE FUNCTION +create operator =(procedure = same, leftarg = ctype, rightarg = ctype); +NOTICE: DDL test: type simple, tag CREATE OPERATOR +create operator class ctype_hash_ops + default for type ctype using hash as + operator 1 =(ctype, ctype); +NOTICE: DDL test: type simple, tag CREATE OPERATOR FAMILY +NOTICE: DDL test: type create operator class, tag CREATE OPERATOR CLASS diff --git a/src/test/modules/test_ddl_deparse/expected/test_ddl_deparse.out b/src/test/modules/test_ddl_deparse/expected/test_ddl_deparse.out new file mode 100644 index 0000000..4a5ea9e --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/test_ddl_deparse.out @@ -0,0 +1,40 @@ +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(); 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(); diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse--1.0.sql b/src/test/modules/test_ddl_deparse/test_ddl_deparse--1.0.sql new file mode 100644 index 0000000..093005a --- /dev/null +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse--1.0.sql @@ -0,0 +1,16 @@ +/* src/test/modules/test_ddl_deparse/test_ddl_deparse--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION test_ddl_deparse" to load this file. \quit + +CREATE FUNCTION get_command_type(pg_ddl_command) + RETURNS text IMMUTABLE STRICT + AS 'MODULE_PATHNAME' LANGUAGE C; + +CREATE FUNCTION get_command_tag(pg_ddl_command) + RETURNS text IMMUTABLE STRICT + AS 'MODULE_PATHNAME' LANGUAGE C; + +CREATE FUNCTION get_altertable_subcmdtypes(pg_ddl_command) + RETURNS text[] IMMUTABLE STRICT + AS 'MODULE_PATHNAME' LANGUAGE C; diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c new file mode 100644 index 0000000..9476c3f --- /dev/null +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c @@ -0,0 +1,296 @@ +/*---------------------------------------------------------------------- + * test_ddl_deparse.c + * Support functions for the test_ddl_deparse module + * + * Copyright (c) 2014-2022, PostgreSQL Global Development Group + * + * IDENTIFICATION + * src/test/modules/test_ddl_deparse/test_ddl_deparse.c + *---------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "catalog/pg_type.h" +#include "tcop/deparse_utility.h" +#include "tcop/utility.h" +#include "utils/builtins.h" + +PG_MODULE_MAGIC; + +PG_FUNCTION_INFO_V1(get_command_type); +PG_FUNCTION_INFO_V1(get_command_tag); +PG_FUNCTION_INFO_V1(get_altertable_subcmdtypes); + +/* + * Return the textual representation of the struct type used to represent a + * command in struct CollectedCommand format. + */ +Datum +get_command_type(PG_FUNCTION_ARGS) +{ + CollectedCommand *cmd = (CollectedCommand *) PG_GETARG_POINTER(0); + const char *type; + + switch (cmd->type) + { + case SCT_Simple: + type = "simple"; + break; + case SCT_AlterTable: + type = "alter table"; + break; + case SCT_Grant: + type = "grant"; + break; + case SCT_AlterOpFamily: + type = "alter operator family"; + break; + case SCT_AlterDefaultPrivileges: + type = "alter default privileges"; + break; + case SCT_CreateOpClass: + type = "create operator class"; + break; + case SCT_AlterTSConfig: + type = "alter text search configuration"; + break; + default: + type = "unknown command type"; + break; + } + + PG_RETURN_TEXT_P(cstring_to_text(type)); +} + +/* + * Return the command tag corresponding to a parse node contained in a + * CollectedCommand struct. + */ +Datum +get_command_tag(PG_FUNCTION_ARGS) +{ + CollectedCommand *cmd = (CollectedCommand *) PG_GETARG_POINTER(0); + + if (!cmd->parsetree) + PG_RETURN_NULL(); + + PG_RETURN_TEXT_P(cstring_to_text(CreateCommandName(cmd->parsetree))); +} + +/* + * Return a text array representation of the subcommands of an ALTER TABLE + * command. + */ +Datum +get_altertable_subcmdtypes(PG_FUNCTION_ARGS) +{ + CollectedCommand *cmd = (CollectedCommand *) PG_GETARG_POINTER(0); + ArrayBuildState *astate = NULL; + ListCell *cell; + + if (cmd->type != SCT_AlterTable) + elog(ERROR, "command is not ALTER TABLE"); + + foreach(cell, cmd->d.alterTable.subcmds) + { + CollectedATSubcmd *sub = lfirst(cell); + AlterTableCmd *subcmd = castNode(AlterTableCmd, sub->parsetree); + const char *strtype; + + switch (subcmd->subtype) + { + case AT_AddColumn: + strtype = "ADD COLUMN"; + break; + case AT_AddColumnRecurse: + strtype = "ADD COLUMN (and recurse)"; + break; + case AT_AddColumnToView: + strtype = "ADD COLUMN TO VIEW"; + break; + case AT_ColumnDefault: + strtype = "ALTER COLUMN SET DEFAULT"; + break; + case AT_CookedColumnDefault: + strtype = "ALTER COLUMN SET DEFAULT (precooked)"; + break; + case AT_DropNotNull: + strtype = "DROP NOT NULL"; + break; + case AT_SetNotNull: + strtype = "SET NOT NULL"; + break; + case AT_CheckNotNull: + strtype = "CHECK NOT NULL"; + break; + case AT_SetStatistics: + strtype = "SET STATS"; + break; + case AT_SetOptions: + strtype = "SET OPTIONS"; + break; + case AT_ResetOptions: + strtype = "RESET OPTIONS"; + break; + case AT_SetStorage: + strtype = "SET STORAGE"; + break; + case AT_DropColumn: + strtype = "DROP COLUMN"; + break; + case AT_DropColumnRecurse: + strtype = "DROP COLUMN (and recurse)"; + break; + case AT_AddIndex: + strtype = "ADD INDEX"; + break; + case AT_ReAddIndex: + strtype = "(re) ADD INDEX"; + break; + case AT_AddConstraint: + strtype = "ADD CONSTRAINT"; + break; + case AT_AddConstraintRecurse: + strtype = "ADD CONSTRAINT (and recurse)"; + break; + case AT_ReAddConstraint: + strtype = "(re) ADD CONSTRAINT"; + break; + case AT_AlterConstraint: + strtype = "ALTER CONSTRAINT"; + break; + case AT_ValidateConstraint: + strtype = "VALIDATE CONSTRAINT"; + break; + case AT_ValidateConstraintRecurse: + strtype = "VALIDATE CONSTRAINT (and recurse)"; + break; + case AT_AddIndexConstraint: + strtype = "ADD CONSTRAINT (using index)"; + break; + case AT_DropConstraint: + strtype = "DROP CONSTRAINT"; + break; + case AT_DropConstraintRecurse: + strtype = "DROP CONSTRAINT (and recurse)"; + break; + case AT_ReAddComment: + strtype = "(re) ADD COMMENT"; + break; + case AT_AlterColumnType: + strtype = "ALTER COLUMN SET TYPE"; + break; + case AT_AlterColumnGenericOptions: + strtype = "ALTER COLUMN SET OPTIONS"; + break; + case AT_ChangeOwner: + strtype = "CHANGE OWNER"; + break; + case AT_ClusterOn: + strtype = "CLUSTER"; + break; + case AT_DropCluster: + strtype = "DROP CLUSTER"; + break; + case AT_SetLogged: + strtype = "SET LOGGED"; + break; + case AT_SetUnLogged: + strtype = "SET UNLOGGED"; + break; + case AT_DropOids: + strtype = "DROP OIDS"; + break; + case AT_SetTableSpace: + strtype = "SET TABLESPACE"; + break; + case AT_SetRelOptions: + strtype = "SET RELOPTIONS"; + break; + case AT_ResetRelOptions: + strtype = "RESET RELOPTIONS"; + break; + case AT_ReplaceRelOptions: + strtype = "REPLACE RELOPTIONS"; + break; + case AT_EnableTrig: + strtype = "ENABLE TRIGGER"; + break; + case AT_EnableAlwaysTrig: + strtype = "ENABLE TRIGGER (always)"; + break; + case AT_EnableReplicaTrig: + strtype = "ENABLE TRIGGER (replica)"; + break; + case AT_DisableTrig: + strtype = "DISABLE TRIGGER"; + break; + case AT_EnableTrigAll: + strtype = "ENABLE TRIGGER (all)"; + break; + case AT_DisableTrigAll: + strtype = "DISABLE TRIGGER (all)"; + break; + case AT_EnableTrigUser: + strtype = "ENABLE TRIGGER (user)"; + break; + case AT_DisableTrigUser: + strtype = "DISABLE TRIGGER (user)"; + break; + case AT_EnableRule: + strtype = "ENABLE RULE"; + break; + case AT_EnableAlwaysRule: + strtype = "ENABLE RULE (always)"; + break; + case AT_EnableReplicaRule: + strtype = "ENABLE RULE (replica)"; + break; + case AT_DisableRule: + strtype = "DISABLE RULE"; + break; + case AT_AddInherit: + strtype = "ADD INHERIT"; + break; + case AT_DropInherit: + strtype = "DROP INHERIT"; + break; + case AT_AddOf: + strtype = "OF"; + break; + case AT_DropOf: + strtype = "NOT OF"; + break; + case AT_ReplicaIdentity: + strtype = "REPLICA IDENTITY"; + break; + case AT_EnableRowSecurity: + strtype = "ENABLE ROW SECURITY"; + break; + case AT_DisableRowSecurity: + strtype = "DISABLE ROW SECURITY"; + break; + case AT_ForceRowSecurity: + strtype = "FORCE ROW SECURITY"; + break; + case AT_NoForceRowSecurity: + strtype = "NO FORCE ROW SECURITY"; + break; + case AT_GenericOptions: + strtype = "SET OPTIONS"; + break; + default: + strtype = "unrecognized"; + break; + } + + astate = + accumArrayResult(astate, CStringGetTextDatum(strtype), + false, TEXTOID, CurrentMemoryContext); + } + + if (astate == NULL) + elog(ERROR, "empty alter table subcommand list"); + + PG_RETURN_ARRAYTYPE_P(makeArrayResult(astate, CurrentMemoryContext)); +} diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.control b/src/test/modules/test_ddl_deparse/test_ddl_deparse.control new file mode 100644 index 0000000..09112ee --- /dev/null +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.control @@ -0,0 +1,4 @@ +comment = 'Test code for DDL deparse feature' +default_version = '1.0' +module_pathname = '$libdir/test_ddl_deparse' +relocatable = true |