Skip to content

Instantly share code, notes, and snippets.

@steve-chavez

steve-chavez/old_compacted.sql

Last active January 14, 2022 14:58
Show Gist options
  • Save steve-chavez/eae6a67ec81b195c133bcb9ff0c917fb to your computer and use it in GitHub Desktop.
Save steve-chavez/eae6a67ec81b195c133bcb9ff0c917fb to your computer and use it in GitHub Desktop.
Download ZIP
schema cache queries (how PostgREST sees your database)
Raw
old_compacted.sql
-- from https://gist.github.com/ruslantalpa/b2f10eb1b5f6dd0fc1c154e071a1c91b
\set API_SCHEMA_NAME test
with tables as (
-- Return a list of entities in the database (tables/views/materialized views)
select
n.nspname as table_schema,
relname as table_name,
case
when c.relkind = 'r' then 'table'
when c.relkind = 'v' then 'view'
when c.relkind = 'm' then 'materialized_view'
else 'unknown'
end::text as type,
c.relkind = 'r' or (c.relkind IN ('v', 'f')) and (pg_relation_is_updatable(c.oid::regclass, false) & 8) = 8
or (exists (
select 1
from pg_trigger
where pg_trigger.tgrelid = c.oid and (pg_trigger.tgtype::integer & 69) = 69)
) as insertable,
d.description
from
pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_description as d on d.objoid = c.oid and d.objsubid = 0
where
c.relkind in ('v', 'r', 'm')
and n.nspname not in ('information_schema', 'pg_catalog')
)
, columns as (
--Returns a list of columns in the database with info about them, based on information_schema.columns view definition
select current_database()::information_schema.sql_identifier as table_catalog,
nc.nspname::information_schema.sql_identifier as table_schema,
c.relname::information_schema.sql_identifier as table_name,
a.attname::information_schema.sql_identifier as column_name,
a.attnum::information_schema.cardinal_number as ordinal_position,
pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data as column_default,
case
when a.attnotnull or t.typtype = 'd'::"char" and t.typnotnull then 'NO'::text
else 'YES'::text
end::information_schema.yes_or_no as is_nullable,
case
when t.typtype = 'd'::"char" then
case
when bt.typelem <> 0::oid and bt.typlen = '-1'::integer then 'ARRAY'::text
when nbt.nspname = 'pg_catalog'::name then format_type(t.typbasetype, null::integer)
else 'USER-DEFINED'::text
end
else
case
when t.typelem <> 0::oid and t.typlen = '-1'::integer then 'ARRAY'::text
when nt.nspname = 'pg_catalog'::name then format_type(a.atttypid, null::integer)
else 'USER-DEFINED'::text
end
end::information_schema.character_data as data_type,
information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number as character_maximum_length,
information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number as character_octet_length,
information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number as numeric_precision,
information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number as numeric_precision_radix,
information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number as numeric_scale,
information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number as datetime_precision,
information_schema._pg_interval_type(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.character_data as interval_type,
null::integer::information_schema.cardinal_number as interval_precision,
null::character varying::information_schema.sql_identifier as character_set_catalog,
null::character varying::information_schema.sql_identifier as character_set_schema,
null::character varying::information_schema.sql_identifier as character_set_name,
case
when nco.nspname is not null then current_database()
else null::name
end::information_schema.sql_identifier as collation_catalog,
nco.nspname::information_schema.sql_identifier as collation_schema,
co.collname::information_schema.sql_identifier as collation_name,
case
when t.typtype = 'd'::"char" then current_database()
else null::name
end::information_schema.sql_identifier as domain_catalog,
case
when t.typtype = 'd'::"char" then nt.nspname
else null::name
end::information_schema.sql_identifier as domain_schema,
case
when t.typtype = 'd'::"char" then t.typname
else null::name
end::information_schema.sql_identifier as domain_name,
current_database()::information_schema.sql_identifier as udt_catalog,
COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier as udt_schema,
COALESCE(bt.typname, t.typname)::information_schema.sql_identifier as udt_name,
null::character varying::information_schema.sql_identifier as scope_catalog,
null::character varying::information_schema.sql_identifier as scope_schema,
null::character varying::information_schema.sql_identifier as scope_name,
null::integer::information_schema.cardinal_number as maximum_cardinality,
a.attnum::information_schema.sql_identifier as dtd_identifier,
'NO'::character varying::information_schema.yes_or_no as is_self_referencing,
'NO'::character varying::information_schema.yes_or_no as is_identity,
null::character varying::information_schema.character_data as identity_generation,
null::character varying::information_schema.character_data as identity_start,
null::character varying::information_schema.character_data as identity_increment,
null::character varying::information_schema.character_data as identity_maximum,
null::character varying::information_schema.character_data as identity_minimum,
null::character varying::information_schema.yes_or_no as identity_cycle,
'NEVER'::character varying::information_schema.character_data as is_generated,
null::character varying::information_schema.character_data as generation_expression,
case
when c.relkind = 'r'::"char" OR (c.relkind = any (array['v'::"char", 'f'::"char"])) and pg_column_is_updatable(c.oid::regclass, a.attnum, false) then 'YES'::text
else 'NO'::text
end::information_schema.yes_or_no as is_updatable,
d.description
from pg_attribute a
left join pg_attrdef ad on a.attrelid = ad.adrelid and a.attnum = ad.adnum
join (pg_class c
join pg_namespace nc on c.relnamespace = nc.oid) on a.attrelid = c.oid
join (pg_type t
join pg_namespace nt on t.typnamespace = nt.oid) on a.atttypid = t.oid
left join (pg_type bt
join pg_namespace nbt on bt.typnamespace = nbt.oid) on t.typtype = 'd'::"char" and t.typbasetype = bt.oid
left join (pg_collation co
join pg_namespace nco on co.collnamespace = nco.oid) on a.attcollation = co.oid and (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name)
left join pg_description as d on d.objoid = c.oid and d.objsubid = a.attnum
where not pg_is_other_temp_schema(nc.oid) and a.attnum > 0 and NOT a.attisdropped and (c.relkind = any (array['m'::"char", 'r'::"char", 'v'::"char", 'f'::"char"]))
)
, synonyms as (
-- Returns a list of view columns and their coresponding (source) table columns.
with view_columns as (
select
c.oid as view_oid,
a.attname::information_schema.sql_identifier as column_name
from pg_attribute a
join pg_class c on a.attrelid = c.oid
join pg_namespace nc on c.relnamespace = nc.oid
where
not pg_is_other_temp_schema(nc.oid)
and a.attnum > 0
and not a.attisdropped
and (c.relkind = 'v'::"char")
and nc.nspname not in ('information_schema', 'pg_catalog')
),
view_column_usage as (
select distinct
v.oid as view_oid,
nv.nspname::information_schema.sql_identifier as view_schema,
v.relname::information_schema.sql_identifier as view_name,
nt.nspname::information_schema.sql_identifier as table_schema,
t.relname::information_schema.sql_identifier as table_name,
a.attname::information_schema.sql_identifier as column_name,
pg_get_viewdef(v.oid)::information_schema.character_data as view_definition
from pg_namespace nv
join pg_class v on nv.oid = v.relnamespace
join pg_depend dv on v.oid = dv.refobjid
join pg_depend dt on dv.objid = dt.objid
join pg_class t on dt.refobjid = t.oid
join pg_namespace nt on t.relnamespace = nt.oid
join pg_attribute a on t.oid = a.attrelid and dt.refobjsubid = a.attnum
where
nv.nspname not in ('information_schema', 'pg_catalog')
and v.relkind = 'v'::"char"
and dv.refclassid = 'pg_class'::regclass::oid
and dv.classid = 'pg_rewrite'::regclass::oid
and dv.deptype = 'i'::"char"
and dv.refobjid <> dt.refobjid
and dt.classid = 'pg_rewrite'::regclass::oid
and dt.refclassid = 'pg_class'::regclass::oid
and (t.relkind = any (array['r'::"char", 'v'::"char", 'f'::"char"]))
),
candidates as (
select
vcu.*,
(
select case when match is not null then coalesce(match[8], match[7], match[4]) end
from regexp_matches(
CONCAT('SELECT ', SPLIT_PART(vcu.view_definition, 'SELECT', 2)),
CONCAT('SELECT.*?((',vcu.table_name,')|(\w+))\.(', vcu.column_name, ')(\s+AS\s+("([^"]+)"|([^, \n\t]+)))?.*?FROM.*?(',vcu.table_schema,'\.|)(\2|',vcu.table_name,'\s+(as\s)?\3)'),
'nsi'
) match
) as view_column_name
from view_column_usage as vcu
)
select
c.table_schema,
c.table_name,
c.column_name as table_column_name,
c.view_schema,
c.view_name,
c.view_column_name
from view_columns as vc, candidates as c
where
vc.view_oid = c.view_oid
and vc.column_name = c.view_column_name
order by c.view_schema, c.view_name, c.table_name, c.view_column_name
)
, source_primary_keys as (
/*
-- CTE to replace information_schema.table_constraints to remove owner limit
*/
with tc as (
select current_database()::information_schema.sql_identifier as constraint_catalog,
nc.nspname::information_schema.sql_identifier as constraint_schema,
c.conname::information_schema.sql_identifier as constraint_name,
current_database()::information_schema.sql_identifier as table_catalog,
nr.nspname::information_schema.sql_identifier as table_schema,
r.relname::information_schema.sql_identifier as table_name,
case c.contype
when 'c'::"char" then 'CHECK'::text
when 'f'::"char" then 'FOREIGN KEY'::text
when 'p'::"char" then 'PRIMARY KEY'::text
when 'u'::"char" then 'UNIQUE'::text
else null::text
end::information_schema.character_data as constraint_type,
case
when c.condeferrable then 'YES'::text
else 'NO'::text
end::information_schema.yes_or_no as is_deferrable,
case
when c.condeferred then 'YES'::text
else 'NO'::text
end::information_schema.yes_or_no as initially_deferred
from pg_namespace nc,
pg_namespace nr,
pg_constraint c,
pg_class r
where nc.oid = c.connamespace and nr.oid = r.relnamespace and c.conrelid = r.oid and (c.contype <> all (array['t'::"char", 'x'::"char"])) and r.relkind = 'r'::"char" and not pg_is_other_temp_schema(nr.oid)
/*--and (pg_has_role(r.relowner, 'USAGE'::text) OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text))*/
union all
select current_database()::information_schema.sql_identifier as constraint_catalog,
nr.nspname::information_schema.sql_identifier as constraint_schema,
(((((nr.oid::text || '_'::text) || r.oid::text) || '_'::text) || a.attnum::text) || '_not_null'::text)::information_schema.sql_identifier as constraint_name,
current_database()::information_schema.sql_identifier as table_catalog,
nr.nspname::information_schema.sql_identifier as table_schema,
r.relname::information_schema.sql_identifier as table_name,
'CHECK'::character varying::information_schema.character_data as constraint_type,
'NO'::character varying::information_schema.yes_or_no as is_deferrable,
'NO'::character varying::information_schema.yes_or_no as initially_deferred
from pg_namespace nr,
pg_class r,
pg_attribute a
where nr.oid = r.relnamespace and r.oid = a.attrelid and a.attnotnull and a.attnum > 0 and not a.attisdropped and r.relkind = 'r'::"char" and not pg_is_other_temp_schema(nr.oid)
/*--and (pg_has_role(r.relowner, 'USAGE'::text) OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text))*/
),
/*
-- CTE to replace information_schema.key_column_usage to remove owner limit
*/
kc as (
select current_database()::information_schema.sql_identifier as constraint_catalog,
ss.nc_nspname::information_schema.sql_identifier as constraint_schema,
ss.conname::information_schema.sql_identifier as constraint_name,
current_database()::information_schema.sql_identifier as table_catalog,
ss.nr_nspname::information_schema.sql_identifier as table_schema,
ss.relname::information_schema.sql_identifier as table_name,
a.attname::information_schema.sql_identifier as column_name,
(ss.x).n::information_schema.cardinal_number as ordinal_position,
case
when ss.contype = 'f'::"char" then information_schema._pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
else null::integer
end::information_schema.cardinal_number as position_in_unique_constraint
from pg_attribute a,
( select r.oid as roid,
r.relname,
r.relowner,
nc.nspname as nc_nspname,
nr.nspname as nr_nspname,
c.oid as coid,
c.conname,
c.contype,
c.conindid,
c.confkey,
c.confrelid,
information_schema._pg_expandarray(c.conkey) as x
from pg_namespace nr,
pg_class r,
pg_namespace nc,
pg_constraint c
where nr.oid = r.relnamespace and r.oid = c.conrelid and nc.oid = c.connamespace and (c.contype = ANY (ARRAY['p'::"char", 'u'::"char", 'f'::"char"])) and r.relkind = 'r'::"char" and not pg_is_other_temp_schema(nr.oid)) ss
where ss.roid = a.attrelid and a.attnum = (ss.x).x and not a.attisdropped
/*--and (pg_has_role(ss.relowner, 'USAGE'::text) OR has_column_privilege(ss.roid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text))*/
)
select
kc.table_schema,
kc.table_name,
kc.column_name
from
/*
--information_schema.table_constraints tc,
--information_schema.key_column_usage kc
*/
tc, kc
where
tc.constraint_type = 'PRIMARY KEY' and
kc.table_name = tc.table_name and
kc.table_schema = tc.table_schema and
kc.constraint_name = tc.constraint_name and
kc.table_schema not in ('pg_catalog', 'information_schema')
)
, primary_keys as (
-- original primary keys (from tables)
select * from source_primary_keys k
union
-- replace the synonyms
select
s.view_schema as table_schema,
s.view_name as table_name,
s.view_column_name as column_name
from source_primary_keys k
left join synonyms s on
k.table_schema = s.table_schema and
k.table_name = s.table_name and
k.column_name = s.table_column_name
)
, source_foreign_keys as (
-- Returns a list of foreign key columns as defined by the foreign key constraints
select ns1.nspname as table_schema,
tab.relname as table_name,
column_info.cols as columns,
ns2.nspname as foreign_table_schema,
other.relname as foreign_table_name,
column_info.refs as foreign_columns
from pg_constraint,
lateral (select array_agg(cols.attname) as cols,
array_agg(cols.attnum) as nums,
array_agg(refs.attname) as refs
from ( select unnest(conkey) as col, unnest(confkey) as ref) k,
lateral (select * from pg_attribute
where attrelid = conrelid and attnum = col)
as cols,
lateral (select * from pg_attribute
where attrelid = confrelid and attnum = ref)
as refs)
as column_info,
lateral (select * from pg_namespace where pg_namespace.oid = connamespace) as ns1,
lateral (select * from pg_class where pg_class.oid = conrelid) as tab,
lateral (select * from pg_class where pg_class.oid = confrelid) as other,
lateral (select * from pg_namespace where pg_namespace.oid = other.relnamespace) as ns2
where confrelid != 0
order by (conrelid, column_info.nums)
)
, foreign_keys as (
-- Returns a list of foreign key columns while also considering column "synonyms"
-- This is mostly used to detect foreign key columns in views
-- original foreign keys (from tables)
select * from source_foreign_keys f
union
-- replace the "foreign" entity with it's synonym
select
f.table_schema, f.table_name, f.columns,
s.view_schema as foreign_table_schema,
s.view_name as foreign_table_name,
array_agg(s.view_column_name) as foreign_columns
from source_foreign_keys f
left join synonyms s on
f.foreign_table_schema = s.table_schema and
f.foreign_table_name = s.table_name and
s.table_column_name = any(f.foreign_columns)
where s.table_schema is not null
group by
f.table_schema, f.table_name, f.columns,
s.view_schema, s.view_name
union
-- replace the "source" entity with it's synonym
select
s.view_schema as table_schema,
s.view_name as table_name,
array_agg(s.view_column_name) as columns,
f.foreign_table_schema, f.foreign_table_name, f.foreign_columns
from source_foreign_keys f
left join synonyms s on
f.table_schema = s.table_schema and
f.table_name = s.table_name and
s.table_column_name = any(f.columns)
where s.table_schema is not null
group by
f.foreign_table_schema, f.foreign_table_name, f.foreign_columns,
s.view_schema, s.view_name
union
-- replace both "source" and "forign" entities with their synonyms
select
sa.view_schema as table_schema,
sa.view_name as table_name,
array_agg(sa.view_column_name) as columns,
sb.view_schema as foreign_table_schema,
sb.view_name as foreign_table_name,
array_agg(sb.view_column_name) as foreign_columns
from source_foreign_keys f
left join synonyms sa on
f.table_schema = sa.table_schema and
f.table_name = sa.table_name and
sa.table_column_name = any(f.columns)
left join synonyms sb on
f.foreign_table_schema = sb.table_schema and
f.foreign_table_name = sb.table_name and
sb.table_column_name = any(f.foreign_columns)
where sa.table_schema is not null and sb.table_schema is not null
group by
f.table_schema, f.table_name, f.columns,
sa.view_schema, sa.view_name,
sb.view_schema, sb.view_name
)
, relations as (
-- all foreign keys represent "child" relations
select
'child' as relation_type,
table_name,
table_schema,
columns,
foreign_table_name,
foreign_table_schema,
foreign_columns,
null as link_table_schema,
null as link_table_name,
null::name[] as link_columns_a,
null::name[] as link_columns_b
from foreign_keys
union
-- by inverting source/foreign entities we get the "parent" relations
select
'parent' as relation_type,
foreign_table_name as table_name,
foreign_table_schema as table_schema,
foreign_columns as columns,
table_name as foreign_table_name,
table_schema as foreign_table_schema,
columns as foreign_columns,
null as link_table_schema,
null as link_table_name,
null::name[] as link_columns_a,
null::name[] as link_columns_b
from foreign_keys
union
-- detect many to many relations by joining the foreign_keys table with itself
-- then join entity becomes the "link table"
select
'many' as relation_type,
a.foreign_table_name as table_name,
a.foreign_table_schema as table_schema,
a.foreign_columns as columns,
b.foreign_table_name as foreign_table_name,
b.foreign_table_schema as foreign_table_schema,
b.foreign_columns as foreign_columns,
a.table_schema as link_table_schema,
a.table_name as link_table_name,
a.columns as link_columns_a,
b.columns as link_columns_b
from foreign_keys a
left join foreign_keys b on
a.table_schema = b.table_schema
and a.table_name = b.table_name
where
a.foreign_table_schema != b.foreign_table_schema
or a.foreign_table_name != b.foreign_table_name
)
, procedures as (
-- rpc functions that can be called
with compound_types as (
select t.oid as ooid, attname as att_name, a.oid::regtype as att_type
from pg_type t
join pg_class on (reltype = t.oid)
join pg_attribute on (attrelid = pg_class.oid)
join pg_type a on (atttypid = a.oid)
)
select
n.nspname as "schema_name",
p.proname as "procedure_name",
(SELECT array_to_json(array_agg(cast(n as regtype))) FROM unnest(p.proargtypes) AS n) as "arg_types",
(SELECT array_to_json(array_agg(cast(n as regtype))) FROM unnest(p.proallargtypes) AS n) as "all_arg_types",
array_to_json(p.proargnames) as "arg_names",
array_to_json(p.proargmodes) as "arg_modes",
p.prorettype::regtype as "return_type",
(
select array_to_json(array_agg(row_to_json(ttt)))
from
(
select c.att_name, c.att_type
from compound_types as c
where c.ooid = p.prorettype
) ttt
) as return_type_components,
d.description as "description",
case
when p.provolatile = 'i' then false
when p.provolatile = 's' then false
else true
end as "is_mutation",
p.proisstrict as "is_strict",
p.proretset as "returns_set"
from pg_proc as p
left join pg_namespace as n on n.oid = p.pronamespace
left join pg_description as d on d.objoid = p.oid and d.objsubid = 0
where n.nspname not in ('pg_catalog', 'information_schema')
)
, table_structure as (
-- combine all the information in a json string
select
array_to_json(array_agg(row_to_json(ttt))) as table_structure
from (
select
t.table_schema,
t.table_name,
t.type,
t.insertable,
t.description
,(
select array_to_json(array_agg(row_to_json("keys")))
from
(
select k.column_name
from primary_keys k
where t.table_schema = k.table_schema and t.table_name = k.table_name
) keys
) as primary_keys
,(
select array_to_json(array_agg(row_to_json("cols")))
from
(
select
c.column_name,
c.data_type,
c.description
from columns c
where t.table_schema = c.table_schema and t.table_name = c.table_name
) cols
) as columns
,(
select array_to_json(array_agg(row_to_json("rels")))
from
(
select
r.relation_type,
-- r.table_name,
-- r.table_schema,
r.columns,
r.foreign_table_name,
r.foreign_table_schema,
r.foreign_columns,
r.link_table_schema,
r.link_table_name,
r.link_columns_a,
r.link_columns_b
from relations r
where t.table_schema = r.table_schema and t.table_name = r.table_name
-- fixed schema
and r.table_schema = :'API_SCHEMA_NAME' and r.foreign_table_schema = :'API_SCHEMA_NAME' and (r.link_table_schema = :'API_SCHEMA_NAME' or r.link_table_schema is null)
) rels
) as relations
from tables t
-- fixed schema
where t.table_schema = :'API_SCHEMA_NAME'
) ttt
)
, rpc as (
select
array_to_json(array_agg(row_to_json(ttt))) as rpc
from (
select *
from procedures p
where p.schema_name = :'API_SCHEMA_NAME'
) ttt
)
select row_to_json(ttt)::text as structure from (
select
(select s.table_structure from table_structure s) as table_structure,
(select s.rpc from rpc s) as rpc
) ttt;
-- the folowing queries can be run with
-- psql "postgresql://localhost:5432/databasename" -f structure.sql
-- select * from tables;
-- select * from columns;
-- select * from primary_keys;
-- select * from source_foreign_keys;
-- select * from foreign_keys;
-- select * from relations;
-- select * from procedures;
-- the query below is best executed like this
-- psql "postgresql://localhost:5432/databasename" -f structure.sql --tuples-only | python -mjson.tool
-- select s.structure::text from structure s;
Raw
x_current_queries.sql
-- These are queries extracted from postgrest DbStructure.hs at version https://github.com/PostgREST/postgrest/tree/b87734343e996f099224d034c78ffe366842e749
-- They need to be merged with the old_compacted.sql version. This can be done later.
-- A first version of the OpenAPI output can be worked with the old_compacted.sql queries.
\set API_SCHEMA test
-- all schemas tables
SELECT
n.nspname AS table_schema,
c.relname AS table_name,
NULL AS table_description,
c.relkind = 'r' OR (c.relkind IN ('v','f'))
AND (pg_relation_is_updatable(c.oid::regclass, FALSE) & 8) = 8
OR (EXISTS
( SELECT 1
FROM pg_trigger
WHERE pg_trigger.tgrelid = c.oid
AND (pg_trigger.tgtype::integer & 69) = 69) ) AS insertable
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','r','m','f')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY table_schema, table_name, insertable
ORDER BY table_schema, table_name;
-- all procs in db-schema
SELECT p.proname as "proc_name",
d.description as "proc_description",
pg_get_function_arguments(p.oid) as "args",
tn.nspname as "rettype_schema",
coalesce(comp.relname, t.typname) as "rettype_name",
p.proretset as "rettype_is_setof",
t.typtype as "rettype_typ",
p.provolatile
FROM pg_proc p
JOIN pg_namespace pn ON pn.oid = p.pronamespace
JOIN pg_type t ON t.oid = p.prorettype
JOIN pg_namespace tn ON tn.oid = t.typnamespace
LEFT JOIN pg_class comp ON comp.oid = t.typrelid
LEFT JOIN pg_catalog.pg_description as d on d.objoid = p.oid
WHERE pn.nspname = :'API_SCHEMA';
-- accessible procs
SELECT p.proname as "proc_name",
d.description as "proc_description",
pg_get_function_arguments(p.oid) as "args",
tn.nspname as "rettype_schema",
coalesce(comp.relname, t.typname) as "rettype_name",
p.proretset as "rettype_is_setof",
t.typtype as "rettype_typ",
p.provolatile
FROM pg_proc p
JOIN pg_namespace pn ON pn.oid = p.pronamespace
JOIN pg_type t ON t.oid = p.prorettype
JOIN pg_namespace tn ON tn.oid = t.typnamespace
LEFT JOIN pg_class comp ON comp.oid = t.typrelid
LEFT JOIN pg_catalog.pg_description as d on d.objoid = p.oid
WHERE
pn.nspname = :'API_SCHEMA' AND
has_function_privilege(p.oid, 'execute');
-- db-schema description
select
description
from pg_catalog.pg_namespace n
left join pg_catalog.pg_description d on d.objoid = n.oid
where
n.nspname = :'API_SCHEMA';
-- accessible tables
select
n.nspname as table_schema,
relname as table_name,
d.description as table_description,
c.relkind = 'r' or (c.relkind IN ('v', 'f')) and (pg_relation_is_updatable(c.oid::regclass, false) & 8) = 8
or (exists (
select 1
from pg_trigger
where pg_trigger.tgrelid = c.oid and (pg_trigger.tgtype::integer & 69) = 69)
) as insertable
from
pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_catalog.pg_description as d on d.objoid = c.oid and d.objsubid = 0
where
c.relkind in ('v', 'r', 'm', 'f')
and n.nspname = :'API_SCHEMA'
and (
pg_has_role(c.relowner, 'USAGE'::text)
or has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
or has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)
)
order by relname;
-- all columns
SELECT DISTINCT
info.table_schema AS schema,
info.table_name AS table_name,
info.column_name AS name,
info.description AS description,
info.ordinal_position AS position,
info.is_nullable::boolean AS nullable,
info.data_type AS col_type,
info.is_updatable::boolean AS updatable,
info.character_maximum_length AS max_len,
info.numeric_precision AS precision,
info.column_default AS default_value,
array_to_string(enum_info.vals, ',') AS enum
FROM (
/*
-- CTE based on pg_catalog to get only Primary and Foreign key columns outside api schema
*/
WITH key_columns AS (
SELECT
r.oid AS r_oid,
c.oid AS c_oid,
n.nspname,
c.relname,
r.conname,
r.contype,
unnest(r.conkey) AS conkey
FROM
pg_catalog.pg_constraint r,
pg_catalog.pg_class c,
pg_catalog.pg_namespace n
WHERE
r.contype IN ('f', 'p', 'u')
AND c.relkind IN ('r', 'v', 'f', 'm')
AND r.conrelid = c.oid
AND c.relnamespace = n.oid
AND n.nspname NOT IN ('pg_catalog', 'information_schema', :'API_SCHEMA')
),
/*
-- CTE based on information_schema.columns
-- changed:
-- remove the owner filter
-- limit columns to the ones in the api schema or PK/FK columns
*/
columns AS (
SELECT current_database()::information_schema.sql_identifier AS table_catalog,
nc.nspname::information_schema.sql_identifier AS table_schema,
c.relname::information_schema.sql_identifier AS table_name,
a.attname::information_schema.sql_identifier AS column_name,
d.description::information_schema.sql_identifier AS description,
a.attnum::information_schema.cardinal_number AS ordinal_position,
pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS column_default,
CASE
WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull THEN 'NO'::text
ELSE 'YES'::text
END::information_schema.yes_or_no AS is_nullable,
CASE
WHEN t.typtype = 'd'::"char" THEN
CASE
WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN 'ARRAY'::text
WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer)
ELSE format_type(a.atttypid, a.atttypmod)
END
ELSE
CASE
WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer)
ELSE format_type(a.atttypid, a.atttypmod)
END
END::information_schema.character_data AS data_type,
information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length,
information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_octet_length,
information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision,
information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision_radix,
information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_scale,
information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS datetime_precision,
information_schema._pg_interval_type(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.character_data AS interval_type,
NULL::integer::information_schema.cardinal_number AS interval_precision,
NULL::character varying::information_schema.sql_identifier AS character_set_catalog,
NULL::character varying::information_schema.sql_identifier AS character_set_schema,
NULL::character varying::information_schema.sql_identifier AS character_set_name,
CASE
WHEN nco.nspname IS NOT NULL THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS collation_catalog,
nco.nspname::information_schema.sql_identifier AS collation_schema,
co.collname::information_schema.sql_identifier AS collation_name,
CASE
WHEN t.typtype = 'd'::"char" THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS domain_catalog,
CASE
WHEN t.typtype = 'd'::"char" THEN nt.nspname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_schema,
CASE
WHEN t.typtype = 'd'::"char" THEN t.typname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_name,
current_database()::information_schema.sql_identifier AS udt_catalog,
COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema,
COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name,
NULL::character varying::information_schema.sql_identifier AS scope_catalog,
NULL::character varying::information_schema.sql_identifier AS scope_schema,
NULL::character varying::information_schema.sql_identifier AS scope_name,
NULL::integer::information_schema.cardinal_number AS maximum_cardinality,
a.attnum::information_schema.sql_identifier AS dtd_identifier,
'NO'::character varying::information_schema.yes_or_no AS is_self_referencing,
'NO'::character varying::information_schema.yes_or_no AS is_identity,
NULL::character varying::information_schema.character_data AS identity_generation,
NULL::character varying::information_schema.character_data AS identity_start,
NULL::character varying::information_schema.character_data AS identity_increment,
NULL::character varying::information_schema.character_data AS identity_maximum,
NULL::character varying::information_schema.character_data AS identity_minimum,
NULL::character varying::information_schema.yes_or_no AS identity_cycle,
'NEVER'::character varying::information_schema.character_data AS is_generated,
NULL::character varying::information_schema.character_data AS generation_expression,
CASE
WHEN c.relkind = 'r'::"char" OR (c.relkind = ANY (ARRAY['v'::"char", 'f'::"char"])) AND pg_column_is_updatable(c.oid::regclass, a.attnum, false) THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_updatable
FROM pg_attribute a
LEFT JOIN key_columns kc ON kc.conkey = a.attnum AND kc.c_oid = a.attrelid
LEFT JOIN pg_catalog.pg_description AS d ON d.objoid = a.attrelid and d.objsubid = a.attnum
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
JOIN (pg_class c
JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid
JOIN (pg_type t
JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid
LEFT JOIN (pg_collation co
JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name)
WHERE
NOT pg_is_other_temp_schema(nc.oid)
AND a.attnum > 0
AND NOT a.attisdropped
AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'm'::"char"]))
AND (nc.nspname = :'API_SCHEMA' OR kc.r_oid IS NOT NULL) /*--filter only columns that are FK/PK or in the api schema */
/*--AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text))*/
)
SELECT
table_schema,
table_name,
column_name,
description,
ordinal_position,
is_nullable,
data_type,
is_updatable,
character_maximum_length,
numeric_precision,
column_default,
udt_name
/*-- FROM information_schema.columns*/
FROM columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
) AS info
LEFT OUTER JOIN (
SELECT
n.nspname AS s,
t.typname AS n,
array_agg(e.enumlabel ORDER BY e.enumsortorder) AS vals
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
GROUP BY s,n
) AS enum_info ON (info.udt_name = enum_info.n)
ORDER BY schema, position;
-- all schemas child relationships
SELECT
ns1.nspname AS table_schema,
tab.relname AS table_name,
column_info.cols AS columns,
ns2.nspname AS foreign_table_schema,
other.relname AS foreign_table_name,
column_info.refs AS foreign_columns
FROM pg_constraint,
LATERAL (
SELECT
array_agg(cols.attname) AS cols,
array_agg(cols.attnum) AS nums,
array_agg(refs.attname) AS refs
FROM ( SELECT unnest(conkey) AS col, unnest(confkey) AS ref) k,
LATERAL (SELECT * FROM pg_attribute WHERE attrelid = conrelid AND attnum = col) AS cols,
LATERAL (SELECT * FROM pg_attribute WHERE attrelid = confrelid AND attnum = ref) AS refs) AS column_info,
LATERAL (SELECT * FROM pg_namespace WHERE pg_namespace.oid = connamespace) AS ns1,
LATERAL (SELECT * FROM pg_class WHERE pg_class.oid = conrelid) AS tab,
LATERAL (SELECT * FROM pg_class WHERE pg_class.oid = confrelid) AS other,
LATERAL (SELECT * FROM pg_namespace WHERE pg_namespace.oid = other.relnamespace) AS ns2
WHERE confrelid != 0
ORDER BY (conrelid, column_info.nums);
-- all pks in all schemas
WITH tc AS (
SELECT
current_database()::information_schema.sql_identifier AS constraint_catalog,
nc.nspname::information_schema.sql_identifier AS constraint_schema,
c.conname::information_schema.sql_identifier AS constraint_name,
current_database()::information_schema.sql_identifier AS table_catalog,
nr.nspname::information_schema.sql_identifier AS table_schema,
r.relname::information_schema.sql_identifier AS table_name,
CASE c.contype
WHEN 'c'::"char" THEN 'CHECK'::text
WHEN 'f'::"char" THEN 'FOREIGN KEY'::text
WHEN 'p'::"char" THEN 'PRIMARY KEY'::text
WHEN 'u'::"char" THEN 'UNIQUE'::text
ELSE NULL::text
END::information_schema.character_data AS constraint_type,
CASE
WHEN c.condeferrable THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_deferrable,
CASE
WHEN c.condeferred THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS initially_deferred
FROM pg_namespace nc,
pg_namespace nr,
pg_constraint c,
pg_class r
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND (c.contype <> ALL (ARRAY['t'::"char", 'x'::"char"])) AND r.relkind = 'r'::"char" AND NOT pg_is_other_temp_schema(nr.oid)
/*--AND (pg_has_role(r.relowner, 'USAGE'::text) OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text))*/
UNION ALL
SELECT current_database()::information_schema.sql_identifier AS constraint_catalog,
nr.nspname::information_schema.sql_identifier AS constraint_schema,
(((((nr.oid::text || '_'::text) || r.oid::text) || '_'::text) || a.attnum::text) || '_not_null'::text)::information_schema.sql_identifier AS constraint_name,
current_database()::information_schema.sql_identifier AS table_catalog,
nr.nspname::information_schema.sql_identifier AS table_schema,
r.relname::information_schema.sql_identifier AS table_name,
'CHECK'::character varying::information_schema.character_data AS constraint_type,
'NO'::character varying::information_schema.yes_or_no AS is_deferrable,
'NO'::character varying::information_schema.yes_or_no AS initially_deferred
FROM pg_namespace nr,
pg_class r,
pg_attribute a
WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND a.attnotnull AND a.attnum > 0 AND NOT a.attisdropped AND r.relkind = 'r'::"char" AND NOT pg_is_other_temp_schema(nr.oid)
/*--AND (pg_has_role(r.relowner, 'USAGE'::text) OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text))*/
),
/*
-- CTE to replace information_schema.key_column_usage to remove owner limit
*/
kc AS (
SELECT current_database()::information_schema.sql_identifier AS constraint_catalog,
ss.nc_nspname::information_schema.sql_identifier AS constraint_schema,
ss.conname::information_schema.sql_identifier AS constraint_name,
current_database()::information_schema.sql_identifier AS table_catalog,
ss.nr_nspname::information_schema.sql_identifier AS table_schema,
ss.relname::information_schema.sql_identifier AS table_name,
a.attname::information_schema.sql_identifier AS column_name,
(ss.x).n::information_schema.cardinal_number AS ordinal_position,
CASE
WHEN ss.contype = 'f'::"char" THEN information_schema._pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
ELSE NULL::integer
END::information_schema.cardinal_number AS position_in_unique_constraint
FROM pg_attribute a,
( SELECT r.oid AS roid,
r.relname,
r.relowner,
nc.nspname AS nc_nspname,
nr.nspname AS nr_nspname,
c.oid AS coid,
c.conname,
c.contype,
c.conindid,
c.confkey,
c.confrelid,
information_schema._pg_expandarray(c.conkey) AS x
FROM pg_namespace nr,
pg_class r,
pg_namespace nc,
pg_constraint c
WHERE nr.oid = r.relnamespace AND r.oid = c.conrelid AND nc.oid = c.connamespace AND (c.contype = ANY (ARRAY['p'::"char", 'u'::"char", 'f'::"char"])) AND r.relkind = 'r'::"char" AND NOT pg_is_other_temp_schema(nr.oid)) ss
WHERE ss.roid = a.attrelid AND a.attnum = (ss.x).x AND NOT a.attisdropped
/*--AND (pg_has_role(ss.relowner, 'USAGE'::text) OR has_column_privilege(ss.roid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text))*/
)
SELECT
kc.table_schema,
kc.table_name,
kc.column_name
FROM
/*
--information_schema.table_constraints tc,
--information_schema.key_column_usage kc
*/
tc, kc
WHERE
tc.constraint_type = 'PRIMARY KEY' AND
kc.table_name = tc.table_name AND
kc.table_schema = tc.table_schema AND
kc.constraint_name = tc.constraint_name AND
kc.table_schema NOT IN ('pg_catalog', 'information_schema');
-- all synonyms on db-schema
with
views as (
select
n.nspname as view_schema,
c.relname as view_name,
r.ev_action as view_definition
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_rewrite r on r.ev_class = c.oid
where (c.relkind in ('v', 'm')) and n.nspname = :'API_SCHEMA'
),
removed_subselects as(
select
view_schema, view_name,
regexp_replace(view_definition, ':subselect {.*?:stmt_len 0} :location \\d+} :res(no|ult)', '', 'g') as x
from views
),
target_lists as(
select
view_schema, view_name,
regexp_split_to_array(x, 'targetList') as x
from removed_subselects
),
last_target_list_wo_tail as(
select
view_schema, view_name,
(regexp_split_to_array(x[array_upper(x, 1)], ':onConflict'))[1] as x
from target_lists
),
target_entries as(
select
view_schema, view_name,
unnest(regexp_split_to_array(x, 'TARGETENTRY')) as entry
from last_target_list_wo_tail
),
results as(
select
view_schema, view_name,
substring(entry from ':resname (.*?) :') as view_colum_name,
substring(entry from ':resorigtbl (.*?) :') as resorigtbl,
substring(entry from ':resorigcol (.*?) :') as resorigcol
from target_entries
)
select
sch.nspname as table_schema,
tbl.relname as table_name,
col.attname as table_column_name,
res.view_schema,
res.view_name,
res.view_colum_name
from results res
join pg_class tbl on tbl.oid::text = res.resorigtbl
join pg_attribute col on col.attrelid = tbl.oid and col.attnum::text = res.resorigcol
join pg_namespace sch on sch.oid = tbl.relnamespace
where resorigtbl <> '0'
order by view_schema, view_name, view_colum_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment