\pset null _null_
\pset format unaligned
SET client_min_messages = warning;
SET ROLE postgres;
select ddlx_create(oid) from pg_cast where castsource = 'text'::regtype order by casttarget;
ddlx_create
CREATE CAST (text AS "char")
  WITH FUNCTION "char"(text)
  AS ASSIGNMENT;
COMMENT ON CAST (text AS "char") IS NULL;

CREATE CAST (text AS name)
  WITH FUNCTION name(text)
  AS IMPLICIT;
COMMENT ON CAST (text AS name) IS NULL;

CREATE CAST (text AS xml)
  WITH FUNCTION xml(text);
COMMENT ON CAST (text AS xml) IS NULL;

CREATE CAST (text AS character)
  WITHOUT FUNCTION
  AS IMPLICIT;
COMMENT ON CAST (text AS character) IS NULL;

CREATE CAST (text AS character varying)
  WITHOUT FUNCTION
  AS IMPLICIT;
COMMENT ON CAST (text AS character varying) IS NULL;

CREATE CAST (text AS regclass)
  WITH FUNCTION regclass(text)
  AS IMPLICIT;
COMMENT ON CAST (text AS regclass) IS NULL;

(6 rows)
select ddlx_drop(oid) from pg_cast where castsource = 'text'::regtype order by casttarget;
ddlx_drop
DROP CAST (text AS "char");

DROP CAST (text AS name);

DROP CAST (text AS xml);

DROP CAST (text AS character);

DROP CAST (text AS character varying);

DROP CAST (text AS regclass);

(6 rows)
CREATE COLLATION "POSIX++" (
  LC_COLLATE = 'POSIX',
  LC_CTYPE = 'POSIX'
);
COMMENT ON COLLATION "POSIX++" IS 'standard POSIX++ collation';
select ddlx_create(oid) from pg_collation where collname in ('POSIX++') order by collname;
ddlx_create
CREATE COLLATION "POSIX++" (
  LC_COLLATE = 'POSIX',
  LC_CTYPE = 'POSIX'
);
COMMENT ON COLLATION "POSIX++" IS 'standard POSIX++ collation';
ALTER COLLATION "POSIX++" OWNER TO postgres;

(1 row)
select ddlx_drop(oid) from pg_collation where collname in ('POSIX++') order by collname;
ddlx_drop
DROP COLLATION "POSIX++";

(1 row)
CREATE DEFAULT CONVERSION "ascii_to_utf8++"
  FOR 'SQL_ASCII' TO 'UTF8' FROM ascii_to_utf8;
COMMENT ON CONVERSION "ascii_to_utf8++" IS 'conversion++ for SQL_ASCII to UTF8';
select ddlx_create(oid) from pg_conversion where conname in ('ascii_to_utf8++') order by conname;
ddlx_create
CREATE DEFAULT CONVERSION "ascii_to_utf8++"
  FOR 'SQL_ASCII' TO 'UTF8' FROM ascii_to_utf8;
COMMENT ON CONVERSION "ascii_to_utf8++" IS 'conversion++ for SQL_ASCII to UTF8';
ALTER CONVERSION "ascii_to_utf8++" OWNER TO postgres;

(1 row)
select ddlx_drop(oid) from pg_conversion where conname in ('ascii_to_utf8++') order by conname;
ddlx_drop
DROP CONVERSION "ascii_to_utf8++";

(1 row)
select ddlx_grants('test_class_r'::regclass::oid);
ddlx_grants
GRANT DELETE ON TABLE test_class_r TO postgres;
GRANT INSERT ON TABLE test_class_r TO postgres;
GRANT REFERENCES ON TABLE test_class_r TO postgres;
GRANT SELECT ON TABLE test_class_r TO postgres;
GRANT TRIGGER ON TABLE test_class_r TO postgres;
GRANT TRUNCATE ON TABLE test_class_r TO postgres;
GRANT UPDATE ON TABLE test_class_r TO postgres;
GRANT DELETE ON TABLE test_class_r TO PUBLIC;
GRANT INSERT ON TABLE test_class_r TO PUBLIC;
GRANT REFERENCES ON TABLE test_class_r TO PUBLIC;
GRANT SELECT ON TABLE test_class_r TO PUBLIC;
GRANT TRIGGER ON TABLE test_class_r TO PUBLIC;
GRANT TRUNCATE ON TABLE test_class_r TO PUBLIC;
GRANT UPDATE ON TABLE test_class_r TO PUBLIC;

(1 row)
create operator family opf1 using btree;
comment on operator family opf1 using btree is 'A comment';
select ddlx_create(oid) from pg_opfamily where opfname='opf1';
ddlx_create
CREATE OPERATOR FAMILY opf1 USING btree;
COMMENT ON OPERATOR FAMILY opf1 USING btree IS 'A comment';
ALTER OPERATOR FAMILY opf1 USING btree OWNER TO postgres;

(1 row)
select ddlx_drop(oid) from pg_opfamily where opfname='opf1';
ddlx_drop
DROP OPERATOR FAMILY opf1 USING btree;

(1 row)
select ddlx_create_language(oid) from pg_language 
 where lanname in ('internal','c','sql') 
 order by lanname;
ddlx_create_language
CREATE OR REPLACE LANGUAGE c
  VALIDATOR fmgr_c_validator;
COMMENT ON LANGUAGE c IS 'dynamically-loaded C functions';
ALTER LANGUAGE c OWNER TO postgres;

CREATE OR REPLACE LANGUAGE internal
  VALIDATOR fmgr_internal_validator;
COMMENT ON LANGUAGE internal IS 'built-in functions';
ALTER LANGUAGE internal OWNER TO postgres;

CREATE OR REPLACE TRUSTED LANGUAGE sql
  VALIDATOR fmgr_sql_validator;
COMMENT ON LANGUAGE sql IS 'SQL-language functions';
ALTER LANGUAGE sql OWNER TO postgres;

(3 rows)
-- database
create database ddlx_testdb with encoding=UTF8 template=template0 lc_collate="POSIX" lc_ctype="POSIX";
comment on database ddlx_testdb is 'DDLX Test Database';
alter database ddlx_testdb owner to postgres;
alter database ddlx_testdb connection limit 1234;
begin;
create user ddlx_test_user4;
grant create on database ddlx_testdb to ddlx_test_user4 with grant option;
select ddlx_create(oid) from pg_database where datname='ddlx_testdb';
ddlx_create
CREATE DATABASE ddlx_testdb WITH
  ENCODING = UTF8
  LC_COLLATE = "C"
  LC_CTYPE = "C";

COMMENT ON DATABASE ddlx_testdb IS 'DDLX Test Database';

ALTER DATABASE ddlx_testdb WITH ALLOW_CONNECTIONS true;
ALTER DATABASE ddlx_testdb WITH CONNECTION LIMIT 1234;
ALTER DATABASE ddlx_testdb WITH IS_TEMPLATE false;
ALTER DATABASE ddlx_testdb SET TABLESPACE pg_default;

ALTER DATABASE ddlx_testdb OWNER TO postgres;
GRANT CREATE ON DATABASE ddlx_testdb TO ddlx_test_user4 WITH GRANT OPTION;
GRANT CONNECT ON DATABASE ddlx_testdb TO postgres;
GRANT CREATE ON DATABASE ddlx_testdb TO postgres;
GRANT TEMPORARY ON DATABASE ddlx_testdb TO postgres;
GRANT CONNECT ON DATABASE ddlx_testdb TO PUBLIC;
GRANT TEMPORARY ON DATABASE ddlx_testdb TO PUBLIC;

(1 row)
abort;
drop database ddlx_testdb;
select ddlx_script(oid) from pg_tablespace where spcname='pg_default';
ddlx_script
BEGIN;

/*
DROP TABLESPACE pg_default;
*/

CREATE TABLESPACE pg_default LOCATION '';
COMMENT ON TABLESPACE pg_default IS NULL;
ALTER TABLESPACE pg_default OWNER TO postgres;

END;

(1 row)
-- schema
create schema ddlx_test_schema1;
comment on schema ddlx_test_schema1 is 'DDLX Test Schema';
grant usage on schema ddlx_test_schema1 to public;
select ddlx_create(oid) from pg_namespace where nspname='ddlx_test_schema1';
ddlx_create
CREATE SCHEMA ddlx_test_schema1;
COMMENT ON SCHEMA ddlx_test_schema1 IS 'DDLX Test Schema';
ALTER SCHEMA ddlx_test_schema1 OWNER TO postgres;
GRANT CREATE ON SCHEMA ddlx_test_schema1 TO postgres;
GRANT USAGE ON SCHEMA ddlx_test_schema1 TO postgres;
GRANT USAGE ON SCHEMA ddlx_test_schema1 TO PUBLIC;

(1 row)
-- look for unidentified objects
select classid::regclass,count(*)
  from (
select classid,objid,ddlx_identify(objid) as obj
  from ddlx_get_dependants((select oid from pg_namespace where nspname='public')) d
) a
 where (a.obj).sql_kind is null group by classid
 order by 2 desc, cast(classid::regclass as text) ;
classid|count
pg_extension|2
(1 row)