command_trigger_regression.sql

-- CREATE TESTS --

CREATE AGGREGATE avgtest(bigint) (
  SFUNC=int8_avg_accum,
  STYPE=numeric[],
  FINALFUNC=numeric_avg,
  INITCOND='{0,0}'
);

--Support function
CREATE FUNCTION testcast(text) RETURNS int4 AS $$BEGIN RETURN 4::int4;END;$$ LANGUAGE plpgsql;
CREATE CAST (text AS int4) WITH FUNCTION testcast(text) AS ASSIGNMENT;

CREATE COLLATION "en_GB2"
 (LC_COLLATE='en_GB.utf8', LC_CTYPE='en_GB.utf8');

CREATE COLLATION "en_GB3" FROM "en_GB";

CREATE CONVERSION test
  FOR 'UTF8'
  TO 'SJIS'
  FROM utf8_to_sjis;

CREATE DEFAULT CONVERSION test2
 FOR 'UTF8'
  TO 'SJIS'
  FROM utf8_to_sjis;

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE EXTENSION file_fdw;
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE FOREIGN DATA WRAPPER file HANDLER file_fdw_handler;
CREATE SERVER file FOREIGN DATA WRAPPER "file_fdw";
CREATE FOREIGN TABLE dict (word text) SERVER "file" OPTIONS ( format 'text', filename '/usr/share/dict/british-english');
CREATE FUNCTION testfunc() RETURNS int AS $$BEGIN RETURN 1;END;$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION testfunc() RETURNS int AS $$BEGIN RETURN 1;END;$$ LANGUAGE plpgsql;
CREATE TABLE test (id int, stuff text);
CREATE INDEX idx_test_1 ON test (id);
CREATE UNIQUE INDEX idx_test_2 ON test (id);

CREATE OPERATOR <<||(
  PROCEDURE = int2xor,
  LEFTARG = int2,
  RIGHTARG = int2,
  COMMUTATOR = #);

CREATE OPERATOR CLASS test_op_class 
   FOR TYPE anyenum USING hash AS
   OPERATOR 1  =,
   FUNCTION 1  hashenum(anyenum);

CREATE OPERATOR FAMILY test_ops USING btree;

CREATE TABLE test2 (id int, stuff text); -- support for testing rules
CREATE RULE "_RETURN" AS
    ON SELECT TO test2
    DO INSTEAD
        SELECT 234 id, 'test'::text stuff;

CREATE ROLE test; -- support for testing schemas
CREATE SCHEMA test;
CREATE SCHEMA AUTHORIZATION test;
CREATE SCHEMA postgres CREATE TABLE test (id int);

CREATE SEQUENCE test_seq;
CREATE TEMP SEQUENCE test_seq2;
CREATE TEMPORARY SEQUENCE test_seq3;

CREATE TABLE AS SELECT 1::int id, ''::text test;

CREATE TEXT SEARCH CONFIGURATION test (
  PARSER = "default"
);

CREATE TEXT SEARCH DICTIONARY test_stem (
   TEMPLATE = snowball,
   language = 'english', stopwords = 'english'
);

CREATE TEXT SEARCH PARSER test_parser (
  START = prsd_start,
  GETTOKEN = prsd_nexttoken,
  END = prsd_end,
  LEXTYPES = prsd_lextype,
  HEADLINE = prsd_headline
);

CREATE TEXT SEARCH TEMPLATE test_template (
  INIT = dsimple_init,
  LEXIZE = dsimple_lexize
);

CREATE OR REPLACE FUNCTION public.dummy_trigger_function() RETURNS trigger AS
$$
BEGIN
	RETURN NEW;
END;
$$ LANGUAGE plpgsql; -- support for testing triggers

CREATE TRIGGER trg_test BEFORE INSERT ON test EXECUTE PROCEDURE public.dummy_trigger_function();

CREATE TYPE test.type_test AS
   (a integer,
    b integer,
    c text);

CREATE USER MAPPING FOR postgres SERVER file OPTIONS (user 'test', password 'password');

CREATE VIEW view_test AS SELECT id, stuff FROM public.test;




-- ALTER TESTS --

ALTER AGGREGATE avgtest(bigint) RENAME TO avgtest2;
ALTER AGGREGATE avgtest2(bigint) OWNER TO test;
ALTER AGGREGATE avgtest2(bigint) SET SCHEMA test;

ALTER COLLATION "en_GB2" RENAME TO en_gb_test;
ALTER COLLATION en_gb_test OWNER TO test;
ALTER COLLATION en_gb_test SET SCHEMA test;

ALTER DOMAIN us_postal_code SET DEFAULT 90210;
ALTER DOMAIN us_postal_code DROP DEFAULT;
ALTER DOMAIN us_postal_code SET NOT NULL;
ALTER DOMAIN us_postal_code DROP NOT NULL;
ALTER DOMAIN us_postal_code ADD CONSTRAINT dummy_constraint CHECK (VALUE ~ '^\d{8}$');
ALTER DOMAIN us_postal_code DROP CONSTRAINT dummy_constraint;
ALTER DOMAIN us_postal_code OWNER TO test;
ALTER DOMAIN us_postal_code SET SCHEMA test;

ALTER EXTENSION file_fdw UPDATE TO '1.0';
ALTER EXTENSION file_fdw SET SCHEMA test;
ALTER EXTENSION file_fdw ADD AGGREGATE avgtest2(bigint);
ALTER EXTENSION file_fdw ADD CAST (text AS int4);
ALTER EXTENSION file_fdw ADD COLLATION en_gb_test; -- fails to add
ALTER EXTENSION file_fdw ADD CONVERSION ascii_to_utf8;
ALTER EXTENSION file_fdw ADD DOMAIN test.us_postal_code;
ALTER EXTENSION file_fdw ADD FOREIGN DATA WRAPPER file;
ALTER EXTENSION file_fdw ADD FOREIGN TABLE dict;
ALTER EXTENSION file_fdw ADD FUNCTION testfunc();
ALTER EXTENSION file_fdw ADD OPERATOR <<||(int2,int2); 
ALTER EXTENSION file_fdw ADD OPERATOR CLASS test_op_class USING hash;
ALTER EXTENSION file_fdw ADD OPERATOR FAMILY test_ops USING btree;
-- ALTER EXTENSION file_fdw ADD PROCEDURAL LANGUAGE -- Can't test
ALTER EXTENSION file_fdw ADD SCHEMA test;
ALTER EXTENSION file_fdw ADD SEQUENCE test_seq;
ALTER EXTENSION file_fdw ADD SERVER file;
ALTER EXTENSION file_fdw ADD TABLE test;
ALTER EXTENSION file_fdw ADD TEXT SEARCH CONFIGURATION test;
ALTER EXTENSION file_fdw ADD TEXT SEARCH DICTIONARY test_stem;
ALTER EXTENSION file_fdw ADD TEXT SEARCH PARSER test_parser;
ALTER EXTENSION file_fdw ADD TEXT SEARCH TEMPLATE test_template;
ALTER EXTENSION file_fdw ADD TYPE test.type_test;
ALTER EXTENSION file_fdw ADD VIEW view_test;
ALTER EXTENSION file_fdw DROP AGGREGATE avgtest2(bigint);
ALTER EXTENSION file_fdw DROP CAST (text AS int4);
ALTER EXTENSION file_fdw DROP COLLATION en_gb_test; -- fails to add
ALTER EXTENSION file_fdw DROP CONVERSION ascii_to_utf8;
ALTER EXTENSION file_fdw DROP DOMAIN test.us_postal_code;
ALTER EXTENSION file_fdw DROP FOREIGN DATA WRAPPER file;
ALTER EXTENSION file_fdw DROP FOREIGN TABLE dict;
ALTER EXTENSION file_fdw DROP FUNCTION testfunc();
ALTER EXTENSION file_fdw DROP OPERATOR <<||(int2,int2); 
ALTER EXTENSION file_fdw DROP OPERATOR CLASS test_op_class USING hash;
ALTER EXTENSION file_fdw DROP OPERATOR FAMILY test_ops USING btree;
-- ALTER EXTENSION file_fdw DROP PROCEDURAL LANGUAGE -- Can't test
ALTER EXTENSION file_fdw DROP SCHEMA test;
ALTER EXTENSION file_fdw DROP SEQUENCE test_seq;
ALTER EXTENSION file_fdw DROP SERVER file;
ALTER EXTENSION file_fdw DROP TABLE test;
ALTER EXTENSION file_fdw DROP TEXT SEARCH CONFIGURATION test;
ALTER EXTENSION file_fdw DROP TEXT SEARCH DICTIONARY test_stem;
ALTER EXTENSION file_fdw DROP TEXT SEARCH PARSER test_parser;
ALTER EXTENSION file_fdw DROP TEXT SEARCH TEMPLATE test_template;
ALTER EXTENSION file_fdw DROP TYPE test.type_test;
ALTER EXTENSION file_fdw DROP VIEW view_test;

CREATE USER supertest SUPERUSER; -- support for alter foreign data wrapper
ALTER FOREIGN DATA WRAPPER file OWNER TO supertest;
ALTER FOREIGN DATA WRAPPER file NO HANDLER:
ALTER FOREIGN DATA WRAPPER file HANDLER test.file_fdw_handler;
ALTER FOREIGN DATA WRAPPER file NO VALIDATOR;
ALTER FOREIGN DATA WRAPPER file VALIDATOR test.file_fdw_validator;
--ALTER FOREIGN DATA WRAPPER file OPTIONS (ADD); -- can't test
--ALTER FOREIGN DATA WRAPPER file OPTIONS (DROP); -- can't test

ALTER FOREIGN TABLE dict RENAME TO dict2;
ALTER FOREIGN TABLE dict2 SET SCHEMA test;
ALTER FOREIGN TABLE test.dict2 OWNER TO test;
ALTER FOREIGN TABLE test.dict2 RENAME COLUMN word TO words;
ALTER FOREIGN TABLE test.dict2 ALTER COLUMN words SET NOT NULL;
ALTER FOREIGN TABLE test.dict2 ALTER COLUMN words DROP NOT NULL;
ALTER FOREIGN TABLE test.dict2 ALTER COLUMN words SET DATA TYPE char(90);
ALTER FOREIGN TABLE test.dict2 DROP COLUMN IF EXISTS test RESTRICT;
ALTER FOREIGN TABLE test.dict2 DROP COLUMN IF EXISTS test CASCADE;
ALTER FOREIGN TABLE test.dict2 ADD COLUMN testcol1 int NULL;
ALTER FOREIGN TABLE test.dict2 ADD COLUMN testcol2 int NOT NULL;
ALTER FOREIGN TABLE test.dict2 DROP COLUMN testcol1;
ALTER FOREIGN TABLE test.dict2 DROP COLUMN testcol2;
--ALTER FOREIGN TABLE test.dict2 OPTIONS (ADD) -- can't test
--ALTER FOREIGN TABLE test.dict2 OPTIONS (DROP) -- can't test
--ALTER FOREIGN TABLE test.dict2 OPTIONS (SET) -- can't test

ALTER FUNCTION testfunc() RENAME TO testfunc2;
ALTER FUNCTION testfunc2() OWNER TO test;
ALTER FUNCTION testfunc2() SET SCHEMA test;
ALTER FUNCTION test.testfunc2() COST 77;

CREATE FUNCTION srf_test() RETURNS SETOF int AS
$$
	SELECT x FROM generate_series(1,12) a(x);
$$ LANGUAGE sql; -- support for alter function row change
ALTER FUNCTION srf_test() ROWS 5;
ALTER FUNCTION srf_test() RESET ALL;
ALTER FUNCTION srf_test() SET work_mem TO 1MB;
ALTER FUNCTION srf_test() CALLED ON NULL INPUT;
ALTER FUNCTION srf_test() RETURNS NULL ON NULL INPUT;
ALTER FUNCTION srf_test() STRICT;
ALTER FUNCTION srf_test() IMMUTABLE;
ALTER FUNCTION srf_test() STABLE;
ALTER FUNCTION srf_test() VOLATILE;
ALTER FUNCTION srf_test() EXTERNAL SECURITY INVOKER;
ALTER FUNCTION srf_test() EXTERNAL SECURITY DEFINER;

ALTER LANGUAGE plpgsql RENAME TO plpgsql2;
ALTER LANGUAGE plpgsql2 OWNER TO test;

ALTER OPERATOR <<|| (int2,int2) OWNER TO test;
ALTER OPERATOR <<|| (int2,int2) SET SCHEMA test;

ALTER OPERATOR CLASS test_op_class USING hash RENAME TO test_op_class2;
ALTER OPERATOR CLASS test_op_class2 USING hash OWNER TO test;
ALTER OPERATOR CLASS test_op_class2 USING hash SET SCHEMA test;

ALTER OPERATOR FAMILY test_ops USING btree RENAME TO test_ops2;
ALTER OPERATOR FAMILY test_ops2 USING btree OWNER TO test;
ALTER OPERATOR FAMILY test_ops2 USING btree SET SCHEMA test;

ALTER SCHEMA test RENAME TO testnew;
ALTER SCHEMA testnew OWNER TO test;

ALTER SEQUENCE test_seq OWNER TO test;
ALTER SEQUENCE test_seq RENAME TO test_seq2;
ALTER SEQUENCE test_seq2 SET SCHEMA testnew;
ALTER SEQUENCE testnew.test_seq2 START WITH 3;
ALTER SEQUENCE testnew.test_seq2 RESTART WITH 4;
ALTER SEQUENCE testnew.test_seq2 MINVALUE 3;
ALTER SEQUENCE testnew.test_seq2 NO MINVALUE;
ALTER SEQUENCE testnew.test_seq2 MAXVALUE 900000;
ALTER SEQUENCE testnew.test_seq2 NO MAXVALUE;
ALTER SEQUENCE testnew.test_seq2 CACHE 876;
ALTER SEQUENCE testnew.test_seq2 CYCLE;
ALTER SEQUENCE testnew.test_seq2 NO CYCLE;
CREATE TABLE testnew.seq_table (id int, test text); -- support for testing ALTER SEQUENCE ... OWNED BY ...
ALTER TABLE testnew.seq_table OWNER TO test; -- support for testing ALTER SEQUENCE ... OWNED BY ...
ALTER SEQUENCE testnew.test_seq2 OWNED BY testnew.seq_table.id;

ALTER SERVER file OWNER TO test;
ALTER SERVER file VERSION '1.0';
--ALTER SERVER file OPTIONS (ADD); -- can't test
--ALTER SERVER file OPTIONS (SET); -- can't test
--ALTER SERVER file OPTIONS (DROP); -- can't test

ALTER TABLE test RENAME TO test9;
ALTER TABLE test9 SET SCHEMA testnew;
ALTER TABLE testnew.test9 RENAME COLUMN stuff TO things;
ALTER TABLE testnew.test9 ADD COLUMN alpha text;
ALTER TABLE testnew.test9 DROP COLUMN alpha;
ALTER TABLE testnew.test9 ALTER COLUMN alpha SET DATA TYPE varchar(300);
ALTER TABLE testnew.test9 ALTER COLUMN alpha SET DEFAULT 'test';
ALTER TABLE testnew.test9 ALTER COLUMN alpha DROP DEFAULT;
ALTER TABLE testnew.test9 ALTER COLUMN alpha SET STATISTICS 78;
ALTER TABLE testnew.test9 ALTER COLUMN alpha SET STORAGE PLAIN;
ALTER TABLE testnew.test9 ALTER COLUMN alpha SET NOT NULL;
ALTER TABLE testnew.test9 ALTER COLUMN alpha DROP NOT NULL;
ALTER TABLE testnew.test9 ALTER COLUMN alpha SET (n_distinct = -0.78);
ALTER TABLE testnew.test9 ALTER COLUMN alpha RESET (n_distinct);
ALTER TABLE testnew.test9 ADD CHECK (id > 2) NOT VALID;
ALTER TABLE testnew.test9 ADD CHECK (id < 800000);
ALTER TABLE testnew.test9 SET WITHOUT CLUSTER;
ALTER TABLE testnew.test9 SET WITH OIDS;
ALTER TABLE testnew.test9 SET WITHOUT OIDS;

ALTER TEXT SEARCH CONFIGURATION test RENAME TO test2;
ALTER TEXT SEARCH CONFIGURATION test2 OWNER TO test;
ALTER TEXT SEARCH CONFIGURATION test2 SET SCHEMA testnew;
ALTER TEXT SEARCH CONFIGURATION testnew.test2 ALTER MAPPING REPLACE english_stem WITH dutch_stem;

ALTER TEXT SEARCH DICTIONARY test_stem RENAME TO test_stem2;
ALTER TEXT SEARCH DICTIONARY test_stem2 OWNER TO test;
ALTER TEXT SEARCH DICTIONARY test_stem2 SET SCHEMA testnew;
ALTER TEXT SEARCH DICTIONARY testnew.test_stem2 ( StopWords = dutch );

ALTER TEXT SEARCH PARSER test_parser RENAME TO test_parser2;
ALTER TEXT SEARCH PARSER test_parser2 SET SCHEMA testnew;

ALTER TEXT SEARCH TEMPLATE test_template RENAME TO test_template2;
ALTER TEXT SEARCH TEMPLATE test_template2 SET SCHEMA testnew;

ALTER TRIGGER trg_test ON testnew.test9 RENAME TO trg_test2;

ALTER TYPE testnew.type_test OWNER TO test;
ALTER TYPE testnew.type_test RENAME TO type_test2;
ALTER TYPE testnew.type_test2 SET SCHEMA public;
ALTER TYPE public.type_test2 RENAME ATTRIBUTE a TO z;
ALTER TYPE public.type_test2 ADD ATTRIBUTE alpha text;
ALTER TYPE public.type_test2 ALTER ATTRIBUTE alpha SET DATA TYPE char(90);
ALTER TYPE public.type_test2 DROP ATTRIBUTE alpha;

ALTER VIEW view_test OWNER TO test;
ALTER VIEW view_test RENAME TO view_test2;
ALTER VIEW view_test2 SET SCHEMA testnew;
ALTER VIEW testnew.view_test2 ALTER COLUMN id SET DEFAULT 9;
ALTER VIEW testnew.view_test2 ALTER COLUMN id DROP DEFAULT;


-- DROP TESTS --

DROP AGGREGATE avgtest2(bigint);
DROP AGGREGATE IF EXISTS avgtest2(bigint);

DROP CAST (text AS int4);
DROP CAST IF EXISTS (text AS int4);

DROP COLLATION testnew.en_gb_test;
DROP COLLATION IF EXISTS testnew.en_gb_test;

DROP CONVERSION IF EXISTS big5_to_euc_tw;

DROP DOMAIN testnew.us_postal_code;
DROP DOMAIN IF EXISTS testnew.us_postal_code;

--DROP EXTENSION file_fdw CASCADE; -- save dropping until later as other things are dependant on it

DROP FOREIGN DATA WRAPPER file;
DROP FOREIGN DATA WRAPPER IF EXISTS file;

DROP FOREIGN TABLE test.dict2;
DROP FOREIGN TABLE IF EXISTS test.dict2;

DROP FUNCTION testnew.testfunc();
DROP FUNCTION IF EXISTS testnew.testfunc();

DROP INDEX testnew.idx_test_1;
DROP INDEX IF EXISTS testnew.idx_test_1;

--DROP LANGUAGE -- can't test

DROP OPERATOR <<|| (int2, int2);
DROP OPERATOR IF EXISTS <<|| (int2, int2);

DROP OPERATOR CLASS test_op_class2 USING hash;
DROP OPERATOR CLASS IF EXISTS test_op_class2 USING hash;

DROP OPERATOR FAMILY testnew.test_ops2 USING btree;
DROP OPERATOR FAMILY IF EXISTS testnew.test_ops2 USING btree;

CREATE ROLE role_test; -- support for testing DROP OWNED BY
CREATE TABLE role_test_table (id int, test text);
ALTER TABLE role_test_table OWNER TO role_test;
DROP OWNED BY role_test;

DROP ROLE role_test;
DROP ROLE IF EXISTS role_test;

CREATE RULE notify_test AS ON UPDATE TO seq_table DO ALSO NOTIFY test; -- support for testing DROP RULE
DROP RULE notify_test ON seq_table;
DROP RULE IF EXISTS notify_test ON seq_table;

CREATE SCHEMA test5; -- support for testing DROP SCHEMA
DROP SCHEMA test5;
DROP SCHEMA IF EXISTS test5;
CREATE SCHEMA test6; -- support for testing DROP SCHEMA ... CASCADE
CREATE TABLE test6.test (id int, test text);
DROP SCHEMA test6 CASCADE;

DROP SEQUENCE testnew.test_seq2;
DROP SEQUENCE IF EXISTS testnew.test_seq2;

DROP SERVER file;
DROP SERVER IF EXISTS file;

CREATE TABLE test (id int, test text); -- support for testing DROP TABLE
DROP TABLE test;
DROP TABLE IF EXISTS test;

DROP TEXT SEARCH CONFIGURATION testnew.test2;
DROP TEXT SEARCH CONFIGURATION IF EXISTS testnew.test2;

DROP TEXT SEARCH DICTIONARY testnew.test_stem2;
DROP TEXT SEARCH DICTIONARY IF EXISTS testnew.test_stem2;

DROP TEXT SEARCH PARSER testnew.test_parser2;
DROP TEXT SEARCH PARSER IF EXISTS testnew.test_parser2;

DROP TEXT SEARCH TEMPLATE testnew.test_template2;
DROP TEXT SEARCH TEMPLATE IF EXISTS testnew.test_template2;

CREATE TABLE test (id int, test text);  -- support for testing DROP TRIGGER
CREATE TRIGGER trg_test BEFORE INSERT ON test EXECUTE PROCEDURE public.dummy_trigger_function(); -- support for testing DROP TRIGGER
DROP TRIGGER trg_test ON test;
DROP TRIGGER IF EXISTS trg_test ON test;

DROP TYPE public.type_test2;
DROP TYPE IF EXISTS public.type_test2;

-- DROP USER MAPPING  -- can't test

DROP VIEW testnew.view_test2;
DROP VIEW IF EXISTS testnew.view_test2;

DROP EXTENSION file_fdw CASCADE;