select
cast(c.oid as varchar) as id,
c.relname AS name,
current_database() as catalog_name,
n.nspname AS schema_name
from pg_class c
left join pg_namespace n
on n.oid = c.relnamespace
where c.relkind = 'r' and n.nspname not in ('pg_catalog','information_schema')
select
cast(a.attrelid as varchar) || '.' || cast (a.attnum as varchar) as id,
cast(c.oid as varchar) as table_id,
a.attname as name,
a.attnum as ordinal,
not a.attnotnull as is_nullable,
t.typname as type_name,
case
when t.typname in ('bpchar', 'varchar') and a.atttypmod != -1 then a.atttypmod - 4
when t.typname in ('bit', 'varbit') and a.atttypmod != -1 then a.atttypmod
else null
end as max_length,
case
when t.typname = 'numeric' and a.atttypmod != -1 then ((a.atttypmod - 4) >> 16) & 65535
else null
end as precision,
case
when t.typname in ('time', 'timestamp', 'timestamptz') and a.atttypmod != -1 then a.atttypmod
when t.typname = 'interval' and a.atttypmod & 65535 != 65535 then a.atttypmod & 65535
else null
end as datetime_precision,
case
when t.typname = 'numeric' and a.atttypmod != -1 then (a.atttypmod - 4) & 65535
else null
end as scale,
case
when t.typlen = -1 and t.typelem != 0 then true
else false
end as is_multiset,
null as collation_catalog_name,
null as collation_schema_name,
null as collation_name,
null as char_set_catalog_name,
null as char_set_schema_name,
null as char_set_name,
case
when pg_get_expr(ad.adbin, ad.adrelid) like 'nextval%' then true
else false
end as is_identity,
false as is_generated,
-- default value column
ad.adsrc as default_value
from pg_attribute a
join pg_class c
on a.attrelid = c.oid
join pg_type t
on a.atttypid = t.oid
left join pg_attrdef ad
on a.attrelid = ad.adrelid and a.attnum = ad.adnum
where t.typtype = 'b' and c.relkind = 'r' and a.attnum >= 0 and c.relnamespace in
(select oid from pg_namespace where nspname not in ('pg_catalog','information_schema'))
select
cast(c.oid as varchar) as id,
cast(c.conrelid as varchar) as table_id,
c.conname as name,
c.condeferrable as is_deferrable,
c.condeferred as is_initially_deferred,
c.contype as constraint_type,
c.consrc as expression,
case c.confupdtype
when 'c' then 'CASCADE'
when 'n' then 'SET NULL'
when 'd' then 'SET DEFAULT'
when 'r' then 'RESTRICT'
when 'a' then 'NO ACTION'
else NULL
end AS update_rule,
case c.confdeltype
when 'c' then 'CASCADE'
when 'n' then 'SET NULL'
when 'd' then 'SET DEFAULT'
when 'r' then 'RESTRICT'
when 'a' then 'NO ACTION'
else NULL
end AS delete_rule
from pg_constraint c
select
cast(c.oid as varchar) || '.' || cast((c.confkey).n as varchar) as id,
(c.confkey).n as ordinal,
cast(pkc.attrelid as varchar) || '.' || cast (pkc.attnum as varchar) as from_columnid,
cast(fkc.attrelid as varchar) || '.' || cast (fkc.attnum as varchar) as to_columnid,
cast(c.oid as varchar) as constraint_id
from (select c.oid,
c.conname,
c.conrelid,
information_schema._pg_expandarray(c.conkey) as conkey,
c.confrelid,
information_schema._pg_expandarray(c.confkey) as confkey
from pg_constraint c
where contype = 'f') c
join pg_class pt on pt.oid = c.conrelid
join pg_class ft on ft.oid = c.confrelid
join pg_attribute pkc on pkc.attrelid = c.conrelid and pkc.attnum = (c.conkey).x
join pg_attribute fkc on fkc.attrelid = c.confrelid and fkc.attnum = (c.confkey).x
select
cast(c.oid as varchar) as id,
c.relname AS name,
current_database() as catalog_name,
n.nspname AS schema_name,
false as is_updatable,
pg_get_viewdef(c.oid) AS definition
from pg_class c
left join pg_namespace n
on n.oid = c.relnamespace
where c.relkind = 'v' and n.nspname not in ('pg_catalog','information_schema')
select
cast(a.attrelid as varchar) || '.' || cast (a.attnum as varchar) as id,
cast(c.oid as varchar) as view_id,
a.attname as name,
a.attnum as ordinal,
not a.attnotnull as is_nullable,
t.typname as type_name,
case
when t.typname in ('bpchar', 'varchar') and a.atttypmod != -1 then a.atttypmod - 4
when t.typname in ('bit', 'varbit') and a.atttypmod != -1 then a.atttypmod
else null
end as max_length,
case
when t.typname = 'numeric' and a.atttypmod != -1 then ((a.atttypmod - 4) >> 16) & 65535
else null
end as precision,
case
when t.typname in ('time', 'timestamp', 'timestamptz') and a.atttypmod != -1 then a.atttypmod
when t.typname = 'interval' and a.atttypmod & 65535 != 65535 then a.atttypmod & 65535
else null
end as datetime_precision,
case
when t.typname = 'numeric' and a.atttypmod != -1 then (a.atttypmod - 4) & 65535
else null
end as scale,
case
when t.typlen = -1 and t.typelem != 0 then true
else false
end as is_multiset,
null as collation_catalog_name,
null as collation_schema_name,
null as collation_name,
null as char_set_catalog_name,
null as char_set_schema_name,
null as char_set_name,
case
when pg_get_expr(ad.adbin, ad.adrelid) like 'nextval%' then true
else false
end as is_identity,
false as is_generated,
-- default value column
ad.adsrc as default_value
from pg_attribute a
join pg_class c
on a.attrelid = c.oid
join pg_type t
on a.atttypid = t.oid
left join pg_attrdef ad
on a.attrelid = ad.adrelid AND a.attnum = ad.adnum
where t.typtype = 'b' and c.relkind = 'v' and a.attnum >= 0 and c.relnamespace in
(select oid from pg_namespace where nspname not in ('pg_catalog','information_schema'))
select '1'::varchar as id, '1'::varchar as view_id, '1'::varchar as name, false as is_deferrable, false as is_initially_deferred, 'p'::varchar as constraint_type, '1'::varchar as expression, '1'::varchar as update_rule, '1'::varchar as delete_rule where 1=0
select '1'::varchar as id, 0 as ordinal where 1=0
select
cast(p.oid as varchar) as id,
current_database() as catalog_name,
n.nspname AS schema_name,
p.proname as name,
false as is_builtin,
false as is_niladic,
t.typname as returntype,
null as max_length,
null as precision,
null as datetime_precision,
null as scale,
case
when t.typlen = -1 and t.typelem != 0 then true
else false
end as is_multiset,
null as collation_catalog_name,
null as collation_schema_name,
null as collation_name,
null as char_set_catalog_name,
null as char_set_schema_name,
null as char_set_name,
false as is_aggregate
from pg_proc p
left join pg_namespace n
on n.oid = p.pronamespace
left join pg_type t
on p.prorettype = t.oid
where (p.proretset = false and t.typname != 'void') and n.nspname not in ('pg_catalog','information_schema') and p.proname not in (select pg_proc.proname from pg_proc group by pg_proc.proname having count(pg_proc.proname) > 1)
select
cast(ss.p_oid as varchar) || '.' || cast((ss.x).n as varchar) as id,
cast(ss.p_oid as varchar) as function_id,
case
when NULLIF(ss.proargnames[(ss.x).n], '') is null then 'x'
else ss.proargnames[(ss.x).n]
end as name,
(ss.x).n as ordinal,
t.typname as type_name,
null as max_length,
null as precision,
null as datetime_precision,
null as scale,
case
when t.typlen = -1 and t.typelem != 0 then true
else false
end as is_multiset,
null as collation_catalog_name,
null as collation_schema_name,
null as collation_name,
null as char_set_catalog_name,
null as char_set_schema_name,
null as char_set_name,
case
when ss.proargmodes IS null then 'IN'
when ss.proargmodes[(ss.x).n] = 'i' then 'IN'
when ss.proargmodes[(ss.x).n] = 'o' then 'OUT'
when ss.proargmodes[(ss.x).n] = 'b' then 'INOUT'
else null
end as mode,
null as default
from pg_type t
join (select
n.nspname AS n_nspname,
p.proname,
p.oid AS p_oid,
p.proargnames,
p.proargmodes,
p.proretset,
p.prorettype,
information_schema._pg_expandarray(COALESCE(p.proallargtypes, p.proargtypes::oid[])) AS x
from pg_namespace n
join pg_proc p
on n.oid = p.pronamespace and p.proname not in (select pg_proc.proname from pg_proc group by pg_proc.proname having count(pg_proc.proname) > 1)) ss
on t.oid = (ss.x).x
join pg_type rt
on ss.prorettype = rt.oid
where (ss.proretset = false and rt.typname != 'void') and ss.n_nspname not in ('pg_catalog','information_schema')
select
cast(p.oid as varchar) as id,
current_database() as catalog_name,
n.nspname AS schema_name,
p.proname as name,
false as is_builtin,
false as is_niladic,
--t.typname as returntype,
false as is_aggregate
from pg_proc p
left join pg_namespace n
on n.oid = p.pronamespace
left join pg_type t
on p.prorettype = t.oid
where (p.proretset = true or t.typname = 'void') and n.nspname not in ('pg_catalog','information_schema') and p.proname not in (select pg_proc.proname from pg_proc group by pg_proc.proname having count(pg_proc.proname) > 1)
select
cast(ss.p_oid as varchar) || '.' || cast((ss.x).n as varchar) as id,
cast(ss.p_oid as varchar) as procedure_id,
case
when NULLIF(ss.proargnames[(ss.x).n], '') is null then 'x'
else ss.proargnames[(ss.x).n]
end as name,
(ss.x).n as ordinal,
t.typname as type_name,
null as max_length,
null as precision,
null as datetime_precision,
null as scale,
case
when t.typlen = -1 and t.typelem != 0 then true
else false
end as is_multiset,
null as collation_catalog_name,
null as collation_schema_name,
null as collation_name,
null as char_set_catalog_name,
null as char_set_schema_name,
null as char_set_name,
case
when ss.proargmodes IS null then 'IN'
when ss.proargmodes[(ss.x).n] = 'i' then 'IN'
when ss.proargmodes[(ss.x).n] = 'o' then 'OUT'
when ss.proargmodes[(ss.x).n] = 'b' then 'INOUT'
else null
end as mode,
null as default
from pg_type t
join (select
n.nspname AS n_nspname,
p.proname,
p.oid AS p_oid,
p.proargnames,
p.proargmodes,
p.proretset,
p.prorettype,
information_schema._pg_expandarray(COALESCE(p.proallargtypes, p.proargtypes::oid[])) AS x
from pg_namespace n
join pg_proc p
on n.oid = p.pronamespace and p.proname not in (select pg_proc.proname from pg_proc group by pg_proc.proname having count(pg_proc.proname) > 1)) ss
on t.oid = (ss.x).x
join pg_type rt
on ss.prorettype = rt.oid
where (ss.proretset = true or rt.typname = 'void') and ss.n_nspname not in ('pg_catalog','information_schema')
select
cast(a.attrelid as varchar) || '.' || cast (a.attnum as varchar) as column_id,
cast(coid as varchar) as constraint_id,
(ss.x).n as ordinal
from pg_attribute a
join (select
r.oid AS roid,
c.oid as coid,
c.conname,
information_schema._pg_expandarray(c.conkey) as x,
r.relnamespace as rrelnamespace
from pg_constraint c
join pg_class r
on r.oid = c.conrelid
where r.relkind = 'r') ss
on a.attrelid = ss.roid and a.attnum = (ss.x).x and not a.attisdropped and rrelnamespace in
(select oid from pg_namespace where nspname not in ('pg_catalog','information_schema'))
select '1'::varchar as column_id, '1'::varchar as constraint_id where 1=0
select '1'::varchar as id, '1'::varchar as constraint_id, '1'::varchar as from_columnid, '1'::varchar as to_columnid, 0 as ordinal where 1=0