Created
August 14, 2019 16:07
-
-
Save Aleyasen/692fc995c14f813c2c54ad6aed1e5224 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
( 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