Skip to content

Instantly share code, notes, and snippets.

@Aleyasen
Created August 14, 2019 16:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Aleyasen/692fc995c14f813c2c54ad6aed1e5224 to your computer and use it in GitHub Desktop.
Save Aleyasen/692fc995c14f813c2c54ad6aed1e5224 to your computer and use it in GitHub Desktop.
( SELECT current_database()::character varying(128) AS table_catalog, nc.nspname::character varying(128) AS table_schema, c.relname::character varying(128) AS table_name, a.attname::character varying(128) AS column_name, a.attnum::integer AS ordinal_position,
CASE
WHEN u.usename = "current_user"()::name THEN ad.adsrc::character varying(4000)
ELSE NULL::character varying::character varying(4000)
END AS column_default,
CASE
WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull THEN 'NO'::character varying
ELSE 'YES'::character varying
END AS is_nullable,
CASE
WHEN t.typtype = 'd'::"char" THEN
CASE
WHEN bt.typelem <> 0::oid AND bt.typlen = -1 THEN 'ARRAY'::character varying
WHEN nbt.nspname = 'pg_catalog'::name THEN
CASE
WHEN t.typbasetype = 16::oid THEN 'boolean'::character varying
WHEN t.typbasetype = 18::oid THEN '"char"'::character varying
WHEN t.typbasetype = 21::oid THEN 'smallint'::character varying
WHEN t.typbasetype = 23::oid THEN 'integer'::character varying
WHEN t.typbasetype = 20::oid THEN 'bigint'::character varying
WHEN t.typbasetype = 700::oid THEN 'real'::character varying
WHEN t.typbasetype = 701::oid THEN 'double precision'::character varying
WHEN t.typbasetype = 1042::oid THEN 'character'::character varying
WHEN t.typbasetype = 1043::oid THEN 'character varying'::character varying
WHEN t.typbasetype = 1082::oid THEN 'date'::character varying
WHEN t.typbasetype = 1083::oid THEN 'time without time zone'::character varying
WHEN t.typbasetype = 1114::oid THEN 'timestamp without time zone'::character varying
WHEN t.typbasetype = 1184::oid THEN 'timestamp with time zone'::character varying
WHEN t.typbasetype = 1700::oid THEN 'numeric'::character varying
ELSE t.typname::character varying(128)
END
ELSE 'USER-DEFINED'::character varying
END
ELSE
CASE
WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ARRAY'::character varying
WHEN nt.nspname = 'pg_catalog'::name THEN
CASE
WHEN a.atttypid = 16::oid THEN 'boolean'::character varying
WHEN a.atttypid = 18::oid THEN '"char"'::character varying
WHEN a.atttypid = 21::oid THEN 'smallint'::character varying
WHEN a.atttypid = 23::oid THEN 'integer'::character varying
WHEN a.atttypid = 20::oid THEN 'bigint'::character varying
WHEN a.atttypid = 700::oid THEN 'real'::character varying
WHEN a.atttypid = 701::oid THEN 'double precision'::character varying
WHEN a.atttypid = 1042::oid THEN 'character'::character varying
WHEN a.atttypid = 1043::oid THEN 'character varying'::character varying
WHEN a.atttypid = 1082::oid THEN 'date'::character varying
WHEN a.atttypid = 1083::oid THEN 'time without time zone'::character varying
WHEN a.atttypid = 1114::oid THEN 'timestamp without time zone'::character varying
WHEN a.atttypid = 1184::oid THEN 'timestamp with time zone'::character varying
WHEN a.atttypid = 1700::oid THEN 'numeric'::character varying
ELSE t.typname::character varying(128)
END
ELSE 'USER-DEFINED'::character varying
END
END::character varying(128) AS data_type,
CASE
WHEN a.atttypmod = -1 THEN NULL::integer
WHEN a.atttypid = 1042::oid OR a.atttypid = 1043::oid THEN a.atttypmod - 4
WHEN a.atttypid = 1560::oid OR a.atttypid = 1562::oid THEN a.atttypmod
ELSE NULL::integer
END AS character_maximum_length,
CASE
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 21::oid THEN 16
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 23::oid THEN 32
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 20::oid THEN 64
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 1700::oid THEN
CASE
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typtypmod
ELSE a.atttypmod
END = -1 THEN NULL::integer
ELSE ((
CASE
WHEN t.typtype = 'd'::"char" THEN t.typtypmod
ELSE a.atttypmod
END - 4) >> 16) & 65535
END
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 700::oid THEN 24
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 701::oid THEN 53
ELSE NULL::integer
END AS numeric_precision,
CASE
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 21::oid OR
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 23::oid OR
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 20::oid OR
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 700::oid OR
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 701::oid THEN 2
WHEN
CASE
WHEN t.typtype = 'd'::"char" THEN t.typbasetype
ELSE a.atttypid
END = 1700::oid THEN 10
ELSE NULL::integer
END AS numeric_precision_radix,
CASE
WHEN a.atttypid = 21::oid OR a.atttypid = 23::oid OR a.atttypid = 20::oid THEN 0
WHEN a.atttypid = 1700::oid THEN
CASE
WHEN a.atttypmod = -1 THEN NULL::integer
ELSE (a.atttypmod - 4) & 65535
END
ELSE NULL::integer
END AS numeric_scale,
CASE
WHEN a.atttypmod = -1 THEN NULL::integer
WHEN a.atttypid = 1083::oid OR a.atttypid = 1114::oid OR a.atttypid = 1184::oid OR a.atttypid = 1266::oid THEN a.atttypmod
WHEN a.atttypid = 1186::oid THEN a.atttypmod & 65535
ELSE NULL::integer
END AS datetime_precision, NULL::character varying AS interval_type, NULL::character varying AS interval_precision, NULL::character varying::character varying(128) AS character_set_catalog, NULL::character varying::character varying(128) AS character_set_schema, NULL::character varying::character varying(128) AS character_set_name, NULL::character varying::character varying(128) AS collation_catalog, NULL::character varying::character varying(128) AS collation_schema, NULL::character varying::character varying(128) AS collation_name,
CASE
WHEN t.typtype = 'd'::"char" THEN t.typname
ELSE NULL::name
END::character varying(128) AS domain_name, d.description::character varying AS remarks
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace nc ON nc.oid = c.relnamespace
JOIN pg_user u ON u.usesysid = c.relowner
JOIN pg_type t ON a.atttypid = t.oid
JOIN pg_namespace nt ON t.typnamespace = nt.oid
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
LEFT JOIN pg_description d ON c.oid = d.objoid AND a.attnum = d.objsubid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid
WHERE a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char")
UNION ALL
SELECT current_database()::character varying(128) AS table_catalog, svv_external_columns.schemaname AS table_schema, svv_external_columns.tablename AS table_name, svv_external_columns.columnname AS column_name, svv_external_columns.columnnum AS ordinal_position, NULL::"unknown" AS column_default, NULL::"unknown" AS is_nullable,
CASE
WHEN "left"(svv_external_columns.external_type::text, 7) = 'varchar'::text THEN 'character varying'::character varying
WHEN "left"(svv_external_columns.external_type::text, 4) = 'char'::text THEN 'character'::character varying
WHEN "left"(svv_external_columns.external_type::text, 7) = 'decimal'::text THEN 'numeric'::character varying
WHEN svv_external_columns.external_type::text = 'float'::text THEN 'real'::character varying
WHEN svv_external_columns.external_type::text = 'double'::text THEN 'double precision'::character varying
WHEN svv_external_columns.external_type::text = 'int'::text OR svv_external_columns.external_type::text = 'int4'::text THEN 'integer'::character varying
WHEN svv_external_columns.external_type::text = 'int2'::text THEN 'smallint'::character varying
ELSE svv_external_columns.external_type
END AS data_type,
CASE
WHEN svv_external_columns.external_type::text = 'varchar'::text THEN -1
WHEN "left"(svv_external_columns.external_type::text, 7) = 'varchar'::text THEN regexp_substr(svv_external_columns.external_type::text, '[0-9]+'::text, 7)::integer
WHEN svv_external_columns.external_type::text = 'char'::text THEN -1
WHEN "left"(svv_external_columns.external_type::text, 4) = 'char'::text THEN regexp_substr(svv_external_columns.external_type::text, '[0-9]+'::text, 4)::integer
WHEN svv_external_columns.external_type::text = 'string'::text THEN 16383
ELSE NULL::integer
END AS character_maximum_length,
CASE
WHEN "left"(svv_external_columns.external_type::text, 7) = 'decimal'::text THEN regexp_substr(svv_external_columns.external_type::text, '[0-9]+'::text, 7)::integer
WHEN svv_external_columns.external_type::text = 'int2'::text THEN 16
WHEN svv_external_columns.external_type::text = 'int'::text OR svv_external_columns.external_type::text = 'int4'::text OR svv_external_columns.external_type::text = 'integer'::text THEN 32
WHEN svv_external_columns.external_type::text = 'bigint'::text THEN 64
WHEN svv_external_columns.external_type::text = 'float'::text THEN 24
WHEN svv_external_columns.external_type::text = 'double'::text THEN 53
ELSE NULL::integer
END AS numeric_precision,
CASE
WHEN svv_external_columns.external_type::text = 'smallint'::text OR svv_external_columns.external_type::text = 'integer'::text OR svv_external_columns.external_type::text = 'bigint'::text OR svv_external_columns.external_type::text = 'float'::text OR svv_external_columns.external_type::text = 'double'::text OR svv_external_columns.external_type::text = 'int'::text THEN 2
WHEN "left"(svv_external_columns.external_type::text, 7) = 'decimal'::text THEN 10
ELSE NULL::integer
END AS numeric_precision_radix,
CASE
WHEN "left"(svv_external_columns.external_type::text, 7) = 'decimal'::text THEN
CASE
WHEN regexp_instr(svv_external_columns.external_type::text, ','::text, 7) = 0 THEN '0'::text
ELSE regexp_substr(svv_external_columns.external_type::text, '[0-9]+'::text, regexp_instr(svv_external_columns.external_type::text, ','::text, 7))
END::integer
WHEN svv_external_columns.external_type::text = 'smallint'::text OR svv_external_columns.external_type::text = 'integer'::text OR svv_external_columns.external_type::text = 'bigint'::text OR svv_external_columns.external_type::text = 'int'::text THEN 0
ELSE NULL::integer
END AS numeric_scale,
CASE
WHEN svv_external_columns.external_type::text = 'timestamp'::text THEN 6
ELSE NULL::integer
END AS datetime_precision, NULL::"unknown" AS interval_type, NULL::"unknown" AS interval_precision, NULL::"unknown" AS character_set_catalog, NULL::"unknown" AS character_set_schema, NULL::"unknown" AS character_set_name, NULL::"unknown" AS collation_catalog, NULL::"unknown" AS collation_schema, NULL::"unknown" AS collation_name, NULL::"unknown" AS domain_name, NULL::"unknown" AS remarks
FROM svv_external_columns)
UNION ALL
SELECT current_database()::character varying(128) AS table_catalog, lbv_columns.schemaname::character varying(128) AS table_schema, lbv_columns.tablename::character varying(128) AS table_name, lbv_columns.columnname::character varying(128) AS column_name, lbv_columns.columnnum AS ordinal_position, NULL::"unknown" AS column_default, NULL::"unknown" AS is_nullable,
CASE
WHEN "left"(lbv_columns.columntype::text, 18) = 'character varying('::text THEN 'character varying'::character varying
WHEN "left"(lbv_columns.columntype::text, 10) = 'character('::text THEN 'character'::character varying
WHEN "left"(lbv_columns.columntype::text, 8) = 'numeric('::text THEN 'numeric'::character varying
ELSE lbv_columns.columntype
END::character varying(128) AS data_type,
CASE
WHEN "left"(lbv_columns.columntype::text, 18) = 'character varying('::text THEN regexp_substr(lbv_columns.columntype::text, '[0-9]+'::text, 19)::integer
WHEN "left"(lbv_columns.columntype::text, 10) = 'character('::text THEN regexp_substr(lbv_columns.columntype::text, '[0-9]+'::text, 11)::integer
ELSE NULL::integer
END AS character_maximum_length,
CASE
WHEN lbv_columns.columntype::text = 'numeric'::text THEN NULL::integer
WHEN "left"(lbv_columns.columntype::text, 7) = 'numeric'::text THEN regexp_substr(lbv_columns.columntype::text, '[0-9]+'::text, 7)::integer
WHEN lbv_columns.columntype::text = 'smallint'::text THEN 16
WHEN lbv_columns.columntype::text = 'integer'::text THEN 32
WHEN lbv_columns.columntype::text = 'bigint'::text THEN 64
WHEN lbv_columns.columntype::text = 'double precision'::text THEN 53
WHEN lbv_columns.columntype::text = 'real'::text THEN 24
ELSE NULL::integer
END AS numeric_precision,
CASE
WHEN lbv_columns.columntype::text = 'smallint'::text OR lbv_columns.columntype::text = 'integer'::text OR lbv_columns.columntype::text = 'bigint'::text OR lbv_columns.columntype::text = 'double precision'::text OR lbv_columns.columntype::text = 'real'::text THEN 2
WHEN "left"(lbv_columns.columntype::text, 7) = 'numeric'::text THEN 10
ELSE NULL::integer
END AS numeric_precision_radix,
CASE
WHEN lbv_columns.columntype::text = 'numeric'::text THEN NULL::integer
WHEN "left"(lbv_columns.columntype::text, 7) = 'numeric'::text THEN
CASE
WHEN regexp_instr(lbv_columns.columntype::text, ','::text, 7) = 0 THEN '0'::text
ELSE regexp_substr(lbv_columns.columntype::text, '[0-9]+'::text, regexp_instr(lbv_columns.columntype::text, ','::text, 7))
END::integer
WHEN lbv_columns.columntype::text = 'smallint'::text OR lbv_columns.columntype::text = 'integer'::text OR lbv_columns.columntype::text = 'bigint'::text THEN 0
ELSE NULL::integer
END AS numeric_scale,
CASE
WHEN lbv_columns.columntype::text = 'timestamp with time zone'::text OR lbv_columns.columntype::text = 'timestamp without time zone'::text THEN 6
ELSE NULL::integer
END AS datetime_precision, NULL::"unknown" AS interval_type, NULL::"unknown" AS interval_precision, NULL::"unknown" AS character_set_catalog, NULL::"unknown" AS character_set_schema, NULL::"unknown" AS character_set_name, NULL::"unknown" AS collation_catalog, NULL::"unknown" AS collation_schema, NULL::"unknown" AS collation_name, NULL::"unknown" AS domain_name, NULL::"unknown" AS remarks
FROM ( SELECT lbv_cols.schemaname, lbv_cols.tablename, lbv_cols.columnname, lbv_cols.columntype, lbv_cols.columnnum
FROM pg_get_late_binding_view_cols() lbv_cols(schemaname name, tablename name, columnname name, columntype character varying, columnnum integer)) lbv_columns;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment