\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 "iso_8859_1_to_utf8++"
  FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
COMMENT ON CONVERSION "iso_8859_1_to_utf8++" IS 'conversion for LATIN1 to UTF8++';
select ddlx_create(oid) from pg_conversion where conname in ('iso_8859_1_to_utf8++') order by conname;
ddlx_create
CREATE DEFAULT CONVERSION "iso_8859_1_to_utf8++"
  FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
COMMENT ON CONVERSION "iso_8859_1_to_utf8++" IS 'conversion for LATIN1 to UTF8++';
ALTER CONVERSION "iso_8859_1_to_utf8++" OWNER TO postgres;

(1 row)
select ddlx_drop(oid) from pg_conversion where conname in ('iso_8859_1_to_utf8++') order by conname;
ddlx_drop
DROP CONVERSION "iso_8859_1_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)
create operator class opc1 for type text using btree family opf1 as storage text;
select ddlx_create(oid) from pg_opclass where opcname='opc1';
ddlx_create
CREATE OPERATOR CLASS opc1 FOR TYPE text USING btree FAMILY opf1 AS STORAGE text;
COMMENT ON OPERATOR CLASS opc1 USING btree IS NULL;
ALTER OPERATOR CLASS opc1 USING btree OWNER TO postgres;

(1 row)
select ddlx_drop(oid) from pg_opclass where opcname='opc1';
ddlx_drop
DROP OPERATOR CLASS opc1 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;
alter database ddlx_testdb set standard_conforming_strings = true;
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 SET standard_conforming_strings=true;

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)
-- apropos
select classid, sql_kind, sql_identifier
  from ddlx_apropos('%test_class%');
classid|sql_kind|sql_identifier
pg_class|SEQUENCE|test_class_r_a_seq
pg_class|TABLE|test_class_r
pg_class|INDEX|test_class_r_pkey
pg_class|INDEX|test_class_r_b_key
pg_class|SEQUENCE|test_class_r2_i_seq
pg_class|TABLE|test_class_r2
pg_class|VIEW|test_class_v
pg_class|VIEW|test_class_v2
pg_class|MATERIALIZED VIEW|test_class_m
pg_class|INDEX|test_class_mi
pg_class|VIEW|test_class_v_opt2
pg_proc|FUNCTION|test_proc_2(integer)
pg_class|FOREIGN TABLE|test_class_f
(13 rows)
select namespace,sql_kind, count(*)
  from ddlx_apropos()
 where namespace not in ('pg_catalog','information_schema')
   and sql_kind <> 'FUNCTION'
 group by 1,2
 order by 1,2;
namespace|sql_kind|count
public|AGGREGATE|2
public|FOREIGN TABLE|1
public|INDEX|7
public|MATERIALIZED VIEW|1
public|SEQUENCE|5
public|TABLE|6
public|TYPE|1
public|VIEW|3
(8 rows)
-- 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|3
(1 row)
 -- schema 2
create schema ddlx_test_schema2;
comment on schema ddlx_test_schema2 is 'DDLX Test Schema 2';
grant usage on schema ddlx_test_schema2 to public;
create extension ltree schema ddlx_test_schema2;
set search_path=ddlx_test_schema2,public;
-- select ddlx_script(oid) from pg_namespace where nspname='ddlx_test_schema2';
set search_path=public;
-- look for unidentified objects 2
select classid::regclass,count(*)
  from (
select classid,objid,ddlx_identify(objid) as obj
  from ddlx_get_dependants((select oid from pg_namespace where nspname='ddlx_test_schema2')) 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|1
(1 row)