十九，表
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
pg模板：
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > 0 THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END) as spcname,
(CASE rel.relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, rel.relkind,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
(CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table
-- Added for partition table
{% if tid %}, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef({{ tid }}::oid) ELSE '' END) AS partition_scheme {% endif %}
FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = {{ scid }}::oid
AND NOT rel.relispartition
{% if tid %} AND rel.oid = {{ tid }}::oid {% endif %}
ORDER BY rel.relname;
实际执行：
SELECT rel.oid, rel.relname AS name,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE AND tgenabled = 'O') AS has_enable_triggers,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
(SELECT count(1) FROM pg_catalog.pg_inherits WHERE inhrelid=rel.oid LIMIT 1) as is_inherits,
(SELECT count(1) FROM pg_catalog.pg_inherits WHERE inhparent=rel.oid LIMIT 1) as is_inherited
FROM pg_catalog.pg_class rel
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = 2200::oid
AND NOT rel.relispartition
ORDER BY rel.relname;
（二）创建
1，常规标签
1，查询所有者：
SELECT
r.oid, r.rolname, r.rolcanlogin, r.rolsuper
FROM
pg_catalog.pg_roles r
ORDER BY r.rolcanlogin, r.rolname
2，查询架构：
SELECT
nsp.oid,
nsp.nspname as name,
pg_catalog.has_schema_privilege(nsp.oid, 'CREATE') as can_create,
pg_catalog.has_schema_privilege(nsp.oid, 'USAGE') as has_usage
FROM
pg_catalog.pg_namespace nsp
WHERE
nspname NOT LIKE E'pg\\_%' AND
NOT (
(nsp.nspname = 'pg_catalog' AND EXISTS
(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pg_class' AND
relnamespace = nsp.oid LIMIT 1)) OR
(nsp.nspname = 'pgagent' AND EXISTS
(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'pga_job' AND
relnamespace = nsp.oid LIMIT 1)) OR
(nsp.nspname = 'information_schema' AND EXISTS
(SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'tables' AND
relnamespace = nsp.oid LIMIT 1))
)
ORDER BY nspname;
3，查询表空间：
SELECT
ts.oid AS oid, spcname AS name, spcowner as owner
FROM
pg_catalog.pg_tablespace ts
ORDER BY name;
2，列标签
1，查询“继承自表”：
SELECT c.oid, c.relname , nspname,
CASE WHEN nspname NOT LIKE 'pg\_%' THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname)
END AS inherits
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid=c.relnamespace
WHERE relkind='r' AND NOT relispartition
AND n.nspname NOT LIKE 'pg\_%' AND n.nspname NOT IN ('information_schema')
ORDER BY relnamespace, c.relname
2，查询数据类型：
SELECT
*
FROM
(SELECT
pg_catalog.format_type(t.oid,NULL) AS typname,
CASE WHEN typelem > 0 THEN typelem ELSE t.oid END as elemoid,
typlen, typtype, t.oid, nspname,
(SELECT COUNT(1) FROM pg_catalog.pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup,
CASE WHEN t.typcollation != 0 THEN TRUE ELSE FALSE END AS is_collatable
FROM
pg_catalog.pg_type t
JOIN
pg_catalog.pg_namespace nsp ON typnamespace=nsp.oid
WHERE
(NOT (typname = 'unknown' AND nspname = 'pg_catalog'))
AND
typisdefined AND typtype IN ('b', 'c', 'd', 'e', 'r')
AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE relnamespace=typnamespace
AND relname = typname AND relkind != 'c') AND
(typname NOT LIKE '_%' OR NOT EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE
relnamespace=typnamespace AND relname = substring(typname FROM 2)::name
AND relkind != 'c'))
AND nsp.nspname != 'information_schema'
UNION SELECT 'smallserial', 0, 2, 'b', 0, 'pg_catalog', false, false
UNION SELECT 'bigserial', 0, 8, 'b', 0, 'pg_catalog', false, false
UNION SELECT 'serial', 0, 4, 'b', 0, 'pg_catalog', false, false
) AS dummy
ORDER BY nspname <> 'pg_catalog', nspname <> 'public', nspname, 1
3，高级标签
1，查询“类型”：
SELECT c.oid,
pg_catalog.quote_ident(n.nspname)||'.'||pg_catalog.quote_ident(c.relname) AS typname
FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
WHERE c.relkind = 'c' AND c.relnamespace=n.oid
AND n.nspname NOT LIKE 'pg\_%' AND n.nspname NOT IN ('information_schema')
ORDER BY typname;
2，查询关系：
SELECT c.oid, pg_catalog.quote_ident(n.nspname)||'.'||pg_catalog.quote_ident(c.relname) AS like_relation
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE c.relnamespace=n.oid
AND c.relkind IN ('r', 'v', 'f')
AND n.nspname NOT LIKE 'pg\_%' AND n.nspname NOT IN ('information_schema')
ORDER BY 1;
4，
5
6
7
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（三）属性
属性较多，需要分部查询。
请求参数：
gid=1
sis=1
did=13799
scid=2200
tid=
1，基础属性：
- 常规、高级、参数
pg模板：
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > 0 THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END) as spcname,
(CASE rel.relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, rel.relkind,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
(CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table
-- Added for partition table
{% if tid %}, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef({{ tid }}::oid) ELSE '' END) AS partition_scheme {% endif %}
FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = {{ scid }}::oid
AND NOT rel.relispartition
{% if tid %} AND rel.oid = {{ tid }}::oid {% endif %}
ORDER BY rel.relname;
实际执行：
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > 0 OR rel.relkind = 'p' THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 91867::oid)
END) as spcname,
(CASE rel.relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = 2200::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relkind,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'toast_tuple_target=([0-9]*)') AS toast_tuple_target,
(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = 2200::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
(CASE WHEN rel.oid <= 16383::oid THEN true ElSE false END) AS is_sys_table
-- Added for partition table
, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef(91868::oid) ELSE '' END) AS partition_scheme FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = 2200::oid
AND NOT rel.relispartition
AND rel.oid = 91868::oid ORDER BY rel.relname;
2，查询表中列信息：
SELECT DISTINCT ON (att.attnum) att.attname as name, att.atttypid, att.attlen, att.attnum, att.attndims,
att.atttypmod, att.attacl, att.attnotnull, att.attoptions, att.attstattarget,
att.attstorage, att.attidentity,
pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval,
pg_catalog.format_type(ty.oid,NULL) AS typname,
pg_catalog.format_type(ty.oid,att.atttypmod) AS displaytypname,
pg_catalog.format_type(ty.oid,att.atttypmod) AS cltype,
CASE WHEN ty.typelem > 0 THEN ty.typelem ELSE ty.oid END as elemoid,
(SELECT nspname FROM pg_catalog.pg_namespace WHERE oid = ty.typnamespace) as typnspname,
ty.typstorage AS defaultstorage,
description, pi.indkey,
(SELECT count(1) FROM pg_catalog.pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup,
CASE WHEN length(coll.collname::text) > 0 AND length(nspc.nspname::text) > 0 THEN
pg_catalog.concat(pg_catalog.quote_ident(nspc.nspname),'.',pg_catalog.quote_ident(coll.collname))
ELSE '' END AS collspcname,
EXISTS(SELECT 1 FROM pg_catalog.pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As is_fk,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=att.attrelid AND sl1.objsubid=att.attnum) AS seclabels,
(CASE WHEN (att.attnum < 1) THEN true ElSE false END) AS is_sys_column,
(CASE WHEN (att.attidentity in ('a', 'd')) THEN 'i' WHEN (att.attgenerated in ('s')) THEN 'g' ELSE 'n' END) AS colconstype,
(CASE WHEN (att.attgenerated in ('s')) THEN pg_catalog.pg_get_expr(def.adbin, def.adrelid) END) AS genexpr, tab.relname as relname,
(CASE WHEN tab.relkind = 'v' THEN true ELSE false END) AS is_view_only,
seq.*
FROM pg_catalog.pg_attribute att
JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN (pg_catalog.pg_depend dep JOIN pg_catalog.pg_class cs ON dep.classid='pg_class'::regclass AND dep.objid=cs.oid AND cs.relkind='S') ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
LEFT OUTER JOIN pg_catalog.pg_collation coll ON att.attcollation=coll.oid
LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
LEFT OUTER JOIN pg_catalog.pg_sequence seq ON cs.oid=seq.seqrelid
LEFT OUTER JOIN pg_catalog.pg_class tab on tab.oid = att.attrelid
WHERE att.attrelid = {{ tid }}::oid
AND att.attnum > 0
AND att.attisdropped IS FALSE
ORDER BY att.attnum;
3，查询约束信息：
SELECT cls.oid,
cls.relname as name,
indnkeyatts as col_count,
CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 91867::oid)
END as spcname,
CASE contype
WHEN 'p' THEN desp.description
WHEN 'u' THEN desp.description
WHEN 'x' THEN desp.description
ELSE des.description
END AS comment,
condeferrable,
condeferred,
conislocal,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = {{ tid }}::oid
AND contype='p'
ORDER BY cls.relname
4，查询安全信息：
SELECT 'attacl' as deftype,
COALESCE(gt.rolname, 'PUBLIC') grantee,
g.rolname grantor,
pg_catalog.array_agg(privilege_type order by privilege_type) as privileges,
pg_catalog.array_agg(is_grantable) as grantable
FROM
(SELECT
d.grantee, d.grantor, d.is_grantable,
CASE d.privilege_type
WHEN 'CONNECT' THEN 'c'
WHEN 'CREATE' THEN 'C'
WHEN 'DELETE' THEN 'd'
WHEN 'EXECUTE' THEN 'X'
WHEN 'INSERT' THEN 'a'
WHEN 'REFERENCES' THEN 'x'
WHEN 'SELECT' THEN 'r'
WHEN 'TEMPORARY' THEN 'T'
WHEN 'TRIGGER' THEN 't'
WHEN 'TRUNCATE' THEN 'D'
WHEN 'UPDATE' THEN 'w'
WHEN 'USAGE' THEN 'U'
ELSE 'UNKNOWN'
END AS privilege_type
FROM
(SELECT attacl
FROM pg_catalog.pg_attribute att
WHERE att.attrelid = {{ tid }}::oid
AND att.attnum = 4::int
) acl,
(SELECT (d).grantee AS grantee, (d).grantor AS grantor, (d).is_grantable
AS is_grantable, (d).privilege_type AS privilege_type FROM (SELECT
pg_catalog.aclexplode(attacl) as d FROM pg_catalog.pg_attribute att
WHERE att.attrelid = {{ tid }}::oid
AND att.attnum = 4::int) a) d
) d
LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
GROUP BY g.rolname, gt.rolname
ORDER BY grantee
（四）更新
（五）删除与级联删除
1，删除
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
2，级联删除
请求参数：
gid=1
sid=1
dis=13700
scid=2200
tid=25297
pg模板：
1,获取属性
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > 0 THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END) as spcname,
(CASE rel.relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, rel.relkind,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
(CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table
-- Added for partition table
{% if tid %}, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef({{ tid }}::oid) ELSE '' END) AS partition_scheme {% endif %}
FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = {{ scid }}::oid
AND NOT rel.relispartition
{% if tid %} AND rel.oid = {{ tid }}::oid {% endif %}
ORDER BY rel.relname;
2,级联删除
DROP TABLE IF EXISTS {{conn|qtIdent(data.schema, data.name)}}{% if cascade %} CASCADE{% endif %};
实际执行：
1,获取属性
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > 0 OR rel.relkind = 'p' THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 13799::oid)
END) as spcname,
(CASE rel.relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = 2200::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relkind,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'toast_tuple_target=([0-9]*)') AS toast_tuple_target,
(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = 2200::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
(CASE WHEN rel.oid <= 16383::oid THEN true ElSE false END) AS is_sys_table
-- Added for partition table
, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef(25297::oid) ELSE '' END) AS partition_scheme FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = 2200::oid
AND NOT rel.relispartition
AND rel.oid = 25297::oid ORDER BY rel.relname;
2,级联删除
DROP TABLE IF EXISTS public.products CASCADE;
（六）备份与恢复
从 pgadmin4 源码来看，表的备份使用了 dump 命令。
查看这里：pgadmin4中的备份与恢复
（七）行总数
请求参数：
gid=1
sid=1
dis=13799
scid=2200
tid=24580
data={'name': 'student', 'schema': 'public'}
pg模板：
SELECT COUNT(*)::text FROM {{ conn|qtIdent(data.schema, data.name) }};
实际执行：
SELECT COUNT(*)::text FROM public.student;
（八）truncate 截断
清空数据表：TRUNCATE
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=89234
pg模板：
1，先查询
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > 0 THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END) as spcname,
(CASE rel.relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids, rel.relkind,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = {{scid}}::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
(CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table
-- Added for partition table
{% if tid %}, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef({{ tid }}::oid) ELSE '' END) AS partition_scheme {% endif %}
FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = {{ scid }}::oid
AND NOT rel.relispartition
{% if tid %} AND rel.oid = {{ tid }}::oid {% endif %}
ORDER BY rel.relname;
2，再清空
TRUNCATE TABLE {{conn|qtIdent(data.schema, data.name)}}{% if identity %} RESTART IDENTITY{% endif %}{% if cascade %} CASCADE{% endif %};
实际执行：
1,
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > 0 OR rel.relkind = 'p' THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 89220::oid)
END) as spcname,
(CASE rel.relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = 2200::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relkind,
(CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select 1 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'parallel_workers=([0-9]*)') AS parallel_workers,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'toast_tuple_target=([0-9]*)') AS toast_tuple_target,
(substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = 2200::oid )||'.'||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels,
(CASE WHEN rel.oid <= 16383::oid THEN true ElSE false END) AS is_sys_table
-- Added for partition table
, (CASE WHEN rel.relkind = 'p' THEN pg_catalog.pg_get_partkeydef(89234::oid) ELSE '' END) AS partition_scheme FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = 2200::oid
AND NOT rel.relispartition
AND rel.oid = 89234::oid ORDER BY rel.relname;
2,
TRUNCATE TABLE public.orders;
二十，列
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=23521
pg模板：
SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,NULL) AS datatype,
att.attnotnull as not_null, att.atthasdef as has_default_val
FROM pg_catalog.pg_attribute att
JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid
JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid='pg_class'::regclass AND objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace
LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
WHERE
att.attrelid = {{ tid|qtLiteral }}::oid
{% if clid %}
AND att.attnum = {{ clid|qtLiteral }}
{% endif %}
{### To show system objects ###}
{% if not show_sys_objects and not has_oids %}
AND att.attnum > 0
{% endif %}
{### To show oids in view data ###}
{% if has_oids %}
AND (att.attnum > 0 OR (att.attname = 'oid' AND att.attnum < 0))
{% endif %}
AND att.attisdropped IS FALSE
ORDER BY att.attnum
- 这里的 show_system_objects 在源码中定义为 False
实际执行：
SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,NULL) AS datatype,
att.attnotnull as not_null, att.atthasdef as has_default_val
FROM pg_catalog.pg_attribute att
JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid
JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid='pg_class'::regclass AND objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace
LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
WHERE
att.attrelid = 25321::oid
AND att.attnum > 0
AND att.attisdropped IS FALSE
ORDER BY att.attnum
（二）创建
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（三）属性
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（四）删除
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
二十一，索引
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
pg模板：
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name,
(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited,
CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description ELSE des.description END AS description
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = {{tid}}::OID
AND conname is NULL
{% if idx %}
AND cls.oid = {{ idx }}::OID
{% endif %}
{% if schema_diff %}
AND CASE WHEN (SELECT COUNT(*) FROM pg_catalog.pg_depend
WHERE objid = cls.oid AND deptype = 'e') > 0 THEN FALSE ELSE TRUE END
{% endif %}
ORDER BY cls.relname
实际执行：
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name,
(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited,
CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description ELSE des.description END AS description
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = 24580::OID
AND conname is NULL
ORDER BY cls.relname
（二）创建
1，常规标签
2，定义标签
1，获取访问方法：
SELECT oid, amname
FROM pg_catalog.pg_am
2，获取操作符类：
操作符类根据上面选中的访问方法查询而来，以选中 btree 为例：
SELECT * FROM (
SELECT am.amname AS index_method,
opc.opcname AS opclass_name,
opc.opcintype::regtype AS indexed_type,
opc.opcdefault AS is_default
FROM pg_am am
JOIN pg_opclass opc ON opc.opcmethod = am.oid
ORDER BY index_method, opclass_name
) AS subquery
WHERE index_method = 'btree' AND is_default = false;
3，获取排序规则：
SELECT --nspname, collname,
CASE WHEN length(nspname::text) > 0 AND length(collname::text) > 0 THEN
pg_catalog.concat(quote_ident(nspname), '.', pg_catalog.quote_ident(collname))
ELSE '' END AS collation
FROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n
WHERE c.collnamespace=n.oid
ORDER BY nspname, collname;
3，点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
data={'name': 'index_test', 'description': '测试', 'is_sys_obj': False, 'spcname': 'pg_default', 'amname': 'btree', 'columns': [{'colname': 'tname', 'collspcname': 'pg_catalog."C"', 'op_class': 'bpchar_pattern_ops', 'sort_order': False, 'nulls': False, 'is_sort_nulls_applicable': True}]}
pg模板：
1，ddl:
CREATE {% if data.indisunique %}UNIQUE {% endif %}INDEX{% if add_not_exists_clause %} IF NOT EXISTS{% endif %} {% if data.isconcurrent %}CONCURRENTLY {% endif %}{{conn|qtIdent(data.name)}}
ON {{conn|qtIdent(data.schema, data.table)}} {% if data.amname %}USING {{conn|qtIdent(data.amname)}}{% endif %}
{% if mode == 'create' %}
({% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(c.colname)}}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.op_class %}
{{c.op_class}}{% endif %}{% if data.amname is defined %}{% if c.sort_order is defined and c.is_sort_nulls_applicable %}{% if c.sort_order %} DESC{% else %} ASC{% endif %}{% endif %}{% if c.nulls is defined and c.is_sort_nulls_applicable %} NULLS {% if c.nulls %}
FIRST{% else %}LAST{% endif %}{% endif %}{% endif %}{% endfor %})
{% if data.include|length > 0 %}
INCLUDE({% for col in data.include %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(col)}}{% endfor %})
{% endif %}
{% else %}
{## We will get indented data from postgres for column ##}
({% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{c.colname}}{% if c.collspcname %} COLLATE {{c.collspcname}}{% endif %}{% if c.op_class %}
{{c.op_class}}{% endif %}{% if c.sort_order is defined %}{% if c.sort_order %} DESC{% else %} ASC{% endif %}{% endif %}{% if c.nulls is defined %} NULLS {% if c.nulls %}
FIRST{% else %}LAST{% endif %}{% endif %}{% endfor %})
{% if data.include|length > 0 %}
INCLUDE({% for col in data.include %}{% if loop.index != 1 %}, {% endif %}{{conn|qtIdent(col)}}{% endfor %})
{% endif %}
{% endif %}
{% if data.fillfactor %}
WITH (FILLFACTOR={{data.fillfactor}})
{% endif %}{% if data.spcname %}
TABLESPACE {{conn|qtIdent(data.spcname)}}{% endif %}{% if data.indconstraint %}
WHERE {{data.indconstraint}}{% endif %};
2，Alter：
{## Alter index to use cluster type ##}
{% if data.indisclustered %}
ALTER TABLE IF EXISTS {{conn|qtIdent(data.schema, data.table)}}
CLUSTER ON {{conn|qtIdent(data.name)}};
{% endif %}
{## Changes description ##}
{% if data.description is defined and data.description %}
COMMENT ON INDEX {{conn|qtIdent(data.schema, data.name)}}
IS {{data.description|qtLiteral}};{% endif %}
3，获取创建后的 oid：
SELECT DISTINCT ON(cls.relname) cls.oid
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
WHERE indrelid = {{tid}}::OID
AND cls.relname = {{data.name|qtLiteral}};
实际执行：
CREATE INDEX index_test
ON public.student USING btree
(tname COLLATE pg_catalog."C" bpchar_pattern_ops ASC NULLS LAST)
TABLESPACE pg_default;
COMMENT ON INDEX public.index_test
IS '测试';
SELECT DISTINCT ON(cls.relname) cls.oid
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
WHERE indrelid = 24580::OID
AND cls.relname = 'index_test';
（三）属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
idc=24604
pg模板：
查询基础属性：
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered,
indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid,
CASE WHEN (length(spcname::text) > 0 OR cls.relkind = 'I') THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END as spcname,
tab.relname as tabname, indclass, con.oid AS conoid,
CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description
ELSE des.description END AS description,
pg_catalog.pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname,
(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
{% if datlastsysoid %}, (CASE WHEN cls.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_idx {% endif %}
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = {{tid}}::OID
AND conname is NULL
{% if idx %}AND cls.oid = {{idx}}::OID {% endif %}
ORDER BY cls.relname
查询定义中列的属性：
SELECT
i.indexrelid,
CASE i.indoption[i.attnum - 1]
WHEN 0 THEN ARRAY['ASC', 'NULLS LAST']
WHEN 1 THEN ARRAY['DESC', 'NULLS LAST']
WHEN 2 THEN ARRAY['ASC', 'NULLS FIRST']
WHEN 3 THEN ARRAY['DESC', 'NULLS FIRST']
ELSE ARRAY['UNKNOWN OPTION' || i.indoption[i.attnum - 1]::text, '']
END::text[] AS options,
i.attnum,
pg_catalog.pg_get_indexdef(i.indexrelid, i.attnum, true) as attdef,
CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,
op.oprname AS oprname,
CASE WHEN length(nspc.nspname::text) > 0 AND length(coll.collname::text) > 0 THEN
pg_catalog.concat(pg_catalog.quote_ident(nspc.nspname), '.', pg_catalog.quote_ident(coll.collname))
ELSE '' END AS collnspname
FROM (
SELECT
indexrelid, i.indoption, i.indclass,
pg_catalog.unnest(ARRAY(SELECT pg_catalog.generate_series(1, i.indnatts) AS n)) AS attnum
FROM
pg_catalog.pg_index i
WHERE i.indexrelid = {{idx}}::OID
) i
LEFT JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[i.attnum - 1])
LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid)
LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[i.attnum])
LEFT JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND a.attnum = i.attnum)
LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
ORDER BY i.attnum;
实际执行：
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered,
indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid,
CASE WHEN (length(spcname::text) > 0 OR cls.relkind = 'I') THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 13799::oid)
END as spcname,
tab.relname as tabname, indclass, con.oid AS conoid,
CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description
ELSE des.description END AS description,
pg_catalog.pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname,
(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
, (CASE WHEN cls.oid <= 16383::oid THEN true ElSE false END) AS is_sys_idx FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = 24580::OID
AND conname is NULL
AND cls.oid = 24604::OID ORDER BY cls.relname
SELECT
i.indexrelid,
CASE i.indoption[i.attnum - 1]
WHEN 0 THEN ARRAY['ASC', 'NULLS LAST']
WHEN 1 THEN ARRAY['DESC', 'NULLS LAST']
WHEN 2 THEN ARRAY['ASC', 'NULLS FIRST']
WHEN 3 THEN ARRAY['DESC', 'NULLS FIRST']
ELSE ARRAY['UNKNOWN OPTION' || i.indoption[i.attnum - 1]::text, '']
END::text[] AS options,
i.attnum,
pg_catalog.pg_get_indexdef(i.indexrelid, i.attnum, true) as attdef,
CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,
op.oprname AS oprname,
CASE WHEN length(nspc.nspname::text) > 0 AND length(coll.collname::text) > 0 THEN
pg_catalog.concat(pg_catalog.quote_ident(nspc.nspname), '.', pg_catalog.quote_ident(coll.collname))
ELSE '' END AS collnspname
FROM (
SELECT
indexrelid, i.indoption, i.indclass,
pg_catalog.unnest(ARRAY(SELECT pg_catalog.generate_series(1, i.indnatts) AS n)) AS attnum
FROM
pg_catalog.pg_index i
WHERE i.indexrelid = 92467::OID
) i
LEFT JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[i.attnum - 1])
LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid)
LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[i.attnum])
LEFT JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND a.attnum = i.attnum)
LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
ORDER BY i.attnum;
（四）删除
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
idc=24604
data={'idx': 24604}
pg模板：
DROP INDEX IF EXISTS {{conn|qtIdent(data.nspname, data.name)}}{% if cascade %} cascade{% endif %};
实际执行：先查再删
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid, indkey, indisclustered,
indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS spcoid,
CASE WHEN (length(spcname::text) > 0 OR cls.relkind = 'I') THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 13799::oid)
END as spcname,
tab.relname as tabname, indclass, con.oid AS conoid,
CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description
ELSE des.description END AS description,
pg_catalog.pg_get_expr(indpred, indrelid, true) as indconstraint, contype, condeferrable, condeferred, amname,
(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
, (CASE WHEN cls.oid <= 16383::oid THEN true ElSE false END) AS is_sys_idx FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = 24580::OID
AND conname is NULL
AND cls.oid = 24604::OID ORDER BY cls.relname
DROP INDEX IF EXISTS public.index_test;
二十二，约束
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
{{ cid || exid }}=xxxxx
constraint_type={{ 'p' || 'f' || 'u' || 'c' || 'x' }}
pg模板：
# 查询主键、唯一、检查约束
SELECT cls.oid, cls.relname as name
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND
dep.objid = cls.oid AND
dep.refobjsubid = '0' AND
dep.refclassid=(SELECT oid
FROM pg_catalog.pg_class
WHERE relname='pg_constraint') AND
dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND
con.oid = dep.refobjid)
WHERE indrelid = {{tid}}::oid
AND contype='{{constraint_type}}'
{% if cid %}
AND cls.oid = {{cid}}::oid
{% endif %}
# 查询外键约束
SELECT ct.oid,
conname as name,
NOT convalidated as convalidated
FROM pg_catalog.pg_constraint ct
WHERE contype='f' AND
conrelid = {{tid}}::oid
ORDER BY conname
# 查询排他约束
SELECT conindid as oid,
conname as name,
NOT convalidated as convalidated
FROM pg_catalog.pg_constraint ct
WHERE contype='x' AND
conrelid = {{tid}}::oid
{% if exid %}
AND conindid = {{exid}}::oid
{% endif %}
ORDER BY conname
更简单的查询方式：
1，根据表名获取 tid:
SELECT oid
FROM pg_class
WHERE relname = 'auth_group';
返回-------------------------
"oid"
"24628"
2，根据 tid 获取表中的所有约束
SELECT
conname AS name,
contype AS type
FROM pg_constraint
WHERE conrelid = '16512'::regclass
返回-------------------------------
"name" "type"
"auth_group_pkey" "p" # 主键约束
"auth_group_name_key" "u" # 唯一约束
# f 外键约束
# c 查询约束
# x 排除约束
3，查询表中主键约束：
SELECT
conname AS name,
pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = '表OID'::regclass
AND contype = 'p'
4，查询表中唯一约束
SELECT
conname AS name,
pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = '表OID'::regclass
AND contype = 'u'
5，查询表中外键约束
SELECT
conname AS name,
pg_get_constraintdef(oid) AS definition
FROM pg_constraint
JOIN pg_class ON conrelid=pg_class.oid
WHERE conrelid = '表OID'::regclass
AND contype = 'f'
6，查询表检查约束
SELECT
conname AS name,
pg_get_constraintdef(oid) AS definition
FROM pg_constraint
JOIN pg_class ON conrelid=pg_class.oid
WHERE conrelid = '表OID'::regclass
AND contype = 'c'
6，查询表排除约束
SELECT oid, conname AS name
FROM pg_constraint
WHERE contype = 'x';
（二）创建
1，创建主键约束
1>常规标签
2>定义标签
1，获取列与包含列：
SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,NULL) AS datatype,
att.attnotnull as not_null, att.atthasdef as has_default_val
FROM pg_catalog.pg_attribute att
JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid
JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid='pg_class'::regclass AND objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace
LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
WHERE
att.attrelid = 24580::oid
AND att.attnum > 0
AND att.attisdropped IS FALSE
ORDER BY att.attnum
- tid=24580
2，获取表空间：
SELECT
ts.oid AS oid, spcname AS name, spcowner as owner
FROM
pg_catalog.pg_tablespace ts
ORDER BY name;
3，获取索引：
SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name,
(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited,
CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description ELSE des.description END AS description
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = 24580::OID
AND conname is NULL
ORDER BY cls.relname
3>点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
data={'columns': [{'column': 'sid'}], 'comment': '测试主键', 'include': [], 'name': 'student_pk', 'spcname': 'pg_default'}
pg模板：
ALTER TABLE IF EXISTS testschema.tablefor_primary_key_cons
ADD CONSTRAINT "Pk_$%{}[]()&*^!@""'`\/#" PRIMARY KEY (col1)
WITH (FILLFACTOR=20)
DEFERRABLE INITIALLY DEFERRED;
COMMENT ON CONSTRAINT "Pk_$%{}[]()&*^!@""'`\/#" ON testschema.tablefor_primary_key_cons
IS 'Comment for create';
实际执行：
ALTER TABLE IF EXISTS public.student
ADD CONSTRAINT student_pk PRIMARY KEY (sid);
COMMENT ON CONSTRAINT student_pk ON public.student
IS '测试主键';
2，创建外键约束
1>常规标签
2>定义标签
- 覆盖索引中的内容是自动生成的，格式为 fki_表名。
3>列标签
1，查詢本地列：
SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,NULL) AS datatype,
att.attnotnull as not_null, att.atthasdef as has_default_val
FROM pg_catalog.pg_attribute att
JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid
JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid='pg_class'::regclass AND objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum
LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace
LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
WHERE
att.attrelid = 24577::oid
AND att.attnum > 0
AND att.attisdropped IS FALSE
ORDER BY att.attnum
2，查询参考，其实就是查询所有表：
SELECT cl.oid as value, pg_catalog.quote_ident(nspname)||'.'||pg_catalog.quote_ident(relname) AS label
FROM pg_catalog.pg_namespace nsp, pg_class cl
WHERE relnamespace=nsp.oid AND relkind in ('r', 'p')
AND nsp.nspname NOT LIKE E'pg\_temp\_%'
AND (nsp.nspname NOT LIKE 'pg\_%' AND nsp.nspname NOT in ('information_schema'))
ORDER BY nspname, relname
3，查询引用，其实就是根据选中的参考表的 oid 查询表中的所有列：
SELECT
a.attname AS name, pg_catalog.format_type(a.atttypid, NULL) AS cltype,
pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval, a.attidentity as clidentity,
pg_catalog.quote_ident(n.nspname)||'.'||pg_catalog.quote_ident(c.relname) as inheritedfrom,
c.oid as inheritedid
FROM
pg_catalog.pg_class c
JOIN
pg_catalog.pg_namespace n ON c.relnamespace=n.oid
JOIN
pg_catalog.pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped AND a.attnum > 0
LEFT OUTER JOIN
pg_catalog.pg_attrdef def ON adrelid=a.attrelid AND adnum=a.attnum
WHERE
c.oid = 24628::OID
4>行动标签
行动不通过查询获得。
5>点击保存
请求参数：
gid=1
sid=1
scid=2200
tid=25280
data={'name': 'c_fk', 'comment': 'd表外键', 'confmatchtype': False, 'columns': [{'local_column': 'emp_id', 'referenced': 'id', 'references': 25273, 'references_table_name': 'public.company6'}], 'confupdtype': 'a', 'confdeltype': 'a', 'autoindex': True, 'coveringindex': 'fki_C'}
- data.references 为主表 OID 。
pg模板：
1,查询 schema 与 table 信息：
SELECT nsp.nspname AS schema,
rel.relname AS table
FROM
pg_catalog.pg_class rel
JOIN pg_catalog.pg_namespace nsp
ON rel.relnamespace = nsp.oid::oid
WHERE rel.oid = {{tid}}::oid
2，创建外键：
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.schema, data.table) }}
ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} FOREIGN KEY ({% for columnobj in data.columns %}{% if loop.index != 1 %}
, {% endif %}{{ conn|qtIdent(columnobj.local_column)}}{% endfor %})
REFERENCES {{ conn|qtIdent(data.remote_schema, data.remote_table) }} ({% for columnobj in data.columns %}{% if loop.index != 1 %}
, {% endif %}{{ conn|qtIdent(columnobj.referenced)}}{% endfor %}){% if data.confmatchtype is defined %} {% if data.confmatchtype %}MATCH FULL{% else %}MATCH SIMPLE{% endif%}{% endif%}{% if data.confupdtype is defined %}
ON UPDATE{% if data.confupdtype == 'a' %}
NO ACTION{% elif data.confupdtype == 'r' %}
RESTRICT{% elif data.confupdtype == 'c' %}
CASCADE{% elif data.confupdtype == 'n' %}
SET NULL{% elif data.confupdtype == 'd' %}
SET DEFAULT{% endif %}{% endif %}{% if data.confdeltype is defined %}
ON DELETE{% if data.confdeltype == 'a' %}
NO ACTION{% elif data.confdeltype == 'r' %}
RESTRICT{% elif data.confdeltype == 'c' %}
CASCADE{% elif data.confdeltype == 'n' %}
SET NULL{% elif data.confdeltype == 'd' %}
SET DEFAULT{% endif %}{% endif %}
{% if data.condeferrable %}
DEFERRABLE{% if data.condeferred %}
INITIALLY DEFERRED{% endif%}
{% endif%}
{% if not data.convalidated %}
NOT VALID{% endif%};
{% if data.comment and data.name %}
COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }}
IS {{ data.comment|qtLiteral }};
{% endif %}
{% if data.autoindex and data.coveringindex%}
CREATE INDEX IF NOT EXISTS {{ conn|qtIdent(data.coveringindex) }}
ON {{ conn|qtIdent(data.schema, data.table) }}({% for columnobj in data.columns %}{% if loop.index != 1 %}
, {% endif %}{{ conn|qtIdent(columnobj.local_column)}}{% endfor %});
{% endif %}
4，获取外键 OID 信息：
SELECT ct.oid,
NOT convalidated as convalidated
FROM pg_catalog.pg_constraint ct
WHERE contype='f' AND
ct.conname = {{ name|qtLiteral }};
实际执行：
1,获取从表 schema 与 table 信息：
SELECT nsp.nspname AS schema,
rel.relname AS table
FROM
pg_catalog.pg_class rel
JOIN pg_catalog.pg_namespace nsp
ON rel.relnamespace = nsp.oid::oid
WHERE rel.oid = 25280::oid
2,获取主表 schema 与 table 信息：
SELECT nsp.nspname AS schema,
rel.relname AS table
FROM
pg_catalog.pg_class rel
JOIN pg_catalog.pg_namespace nsp
ON rel.relnamespace = nsp.oid::oid
WHERE rel.oid = 25273::oid
3，创建外键
ALTER TABLE IF EXISTS public.department1
ADD CONSTRAINT c_fk FOREIGN KEY (emp_id)
REFERENCES public.company6 (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
COMMENT ON CONSTRAINT c_fk ON public.department1
IS 'd表外键';
CREATE INDEX IF NOT EXISTS "fki_C"
ON public.department1(emp_id);
4，获取外键 OID 信息：
SELECT ct.oid,
NOT convalidated as convalidated
FROM pg_catalog.pg_constraint ct
WHERE contype='f' AND
ct.conname = 'c_fk';
3，创建唯一约束
1>常规标签
2>定义标签
3>点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
data={'columns': [{'column': 'sid'}], 'comment': '测试唯一约束', 'include': [], 'name': 'student_uk', 'spcname': 'pg_default'}
pg模板：
在这里插入代码片
实际执行：
ALTER TABLE IF EXISTS public.student
ADD CONSTRAINT student_uk UNIQUE (sid);
COMMENT ON CONSTRAINT student_uk ON public.student
IS '测试唯一约束';
4，创建检查约束
1>常规标签
2>定义标签
3>点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
data={'comment': '检查约束', 'consrc': 'sid > 0', 'convalidated': True, 'name': 'student_ck', 'schema': 'public', 'table': 'student'}
pg模板：
{% if data %}
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.schema, data.table) }}
ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} CHECK ({{ data.consrc }}){% if data.convalidated %}
NOT VALID{% endif %}{% if data.connoinherit %} NO INHERIT{% endif %};
{% endif %}
{% if data.comment %}
COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }}
IS {{ data.comment|qtLiteral }};
{% endif %}
SELECT ct.oid,
ct.conname as name,
NOT convalidated as convalidated
FROM pg_catalog.pg_constraint ct
WHERE contype='c' AND
conrelid = {{tid}}::oid LIMIT 1;
实际执行：
ALTER TABLE IF EXISTS public.student
ADD CONSTRAINT student_ck CHECK (sid > 0)
NOT VALID;
COMMENT ON CONSTRAINT student_ck ON public.student
IS '检查约束';
# 查出并返回 oid
SELECT
oid, conname as name,
NOT convalidated as convalidated
FROM
pg_catalog.pg_constraint
WHERE
conrelid = 24580::oid
AND conname='student_ck';
5，创建排他约束
1>常规标签
2>定义标签
获取访问方法：
在这里插入代码片
3>列标签
获取操作符：
在这里插入代码片
3>点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=25264
data={'name': 't2_ek', 'comment': '测试排他约束', 'spcname': 'pg_default', 'amname': 'gist', 'columns': [{'column': 'c1', 'order': False, 'nulls_order': False, 'operator': '=', 'col_type': 'integer'}, {'column': 'c2', 'order': False, 'nulls_order': False, 'operator': '<>', 'col_type': 'text'}], 'include': []}
pg模板：
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.schema, data.table) }}
ADD{% if data.name %} CONSTRAINT {{ conn|qtIdent(data.name) }}{% endif%} EXCLUDE {% if data.amname and data.amname != '' %}USING {{data.amname}}{% endif %} (
{% for col in data.columns %}{% if loop.index != 1 %},
{% endif %}{% if col.is_exp %}{{col.column}}{% else %}{{ conn|qtIdent(col.column)}}{% endif %}{% if col.oper_class and col.oper_class != '' %} {{col.oper_class}}{% endif%}{% if col.order is defined and col.is_sort_nulls_applicable %}{% if col.order %} ASC{% else %} DESC{% endif %} NULLS{% endif %} {% if col.nulls_order is defined and col.is_sort_nulls_applicable %}{% if col.nulls_order %}FIRST {% else %}LAST {% endif %}{% endif %}WITH {{col.operator}}{% endfor %}){% if data.fillfactor %}
WITH (FILLFACTOR={{data.fillfactor}}){% endif %}{% if data.spcname and data.spcname != "pg_default" %}
USING INDEX TABLESPACE {{ conn|qtIdent(data.spcname) }}{% endif %}{% if data.indconstraint %}
WHERE ({{data.indconstraint}}){% endif%}
{% if data.condeferrable %}
DEFERRABLE{% if data.condeferred %}
INITIALLY DEFERRED{% endif%}{% endif%};
{% if data.comment and data.name %}
COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }}
IS {{ data.comment|qtLiteral }};
{% endif %}
实际执行：
ALTER TABLE IF EXISTS public.t2
ADD CONSTRAINT t2_ek EXCLUDE USING gist (
c1 WITH =,
c2 WITH <>);
COMMENT ON CONSTRAINT t2_ek ON public.t2
IS '测试排他约束';
同时返回oid等信息：
SELECT ct.conindid AS oid
FROM pg_catalog.pg_constraint ct
WHERE contype='x' AND
ct.conname = 't2_ek';
（三）属性
1，主键约束属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
cid=24620
constraint_type='p' # 查询主键
pg模板：
1，查询基本信息：
SELECT cls.oid,
cls.relname as name,
indnatts as col_count,
CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END as spcname,
CASE contype
WHEN 'p' THEN desp.description
WHEN 'u' THEN desp.description
WHEN 'x' THEN desp.description
ELSE des.description
END AS comment,
condeferrable,
condeferred,
conislocal,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = {{tid}}::oid
{% if cid %}
AND cls.oid = {{cid}}::oid
{% endif %}
AND contype='{{constraint_type}}'
ORDER BY cls.relname
2，查询列信息：
{###
We need outer SELECT & dummy column to preserve the ordering
because we lose ordering when we use UNION
###}
SELECT * FROM (
{% for n in range(colcnt|int) %}
{% if loop.index != 1 %}
UNION SELECT pg_catalog.pg_get_indexdef({{ cid|string }}, {{ loop.index|string }}, true) AS column, {{ n }} AS dummy
{% else %}
SELECT pg_catalog.pg_get_indexdef({{ cid|string }} , {{ loop.index|string }} , true) AS column, {{ n }} AS dummy
{% endif %}
{% endfor %}
) tmp
ORDER BY dummy
实际执行：
SELECT cls.oid,
cls.relname as name,
indnkeyatts as col_count,
CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 13799::oid)
END as spcname,
CASE contype
WHEN 'p' THEN desp.description
WHEN 'u' THEN desp.description
WHEN 'x' THEN desp.description
ELSE des.description
END AS comment,
condeferrable,
condeferred,
conislocal,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = 24580::oid
AND cls.oid = 24620::oid
AND contype='p'
ORDER BY cls.relname
SELECT * FROM (
SELECT pg_catalog.pg_get_indexdef(24620 , 1 , true) AS column, 0 AS dummy
) tmp
ORDER BY dummy
2，外键约束属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24580
fkid=25285
pg模板：
1,查询外键基本属性
SELECT ct.oid,
conname as name,
condeferrable,
condeferred,
confupdtype,
confdeltype,
CASE confmatchtype
WHEN 's' THEN FALSE
WHEN 'f' THEN TRUE
END AS confmatchtype,
conkey,
confkey,
confrelid,
nl.nspname as fknsp,
cl.relname as fktab,
nr.oid as refnspoid,
nr.nspname as refnsp,
cr.relname as reftab,
description as comment,
convalidated,
conislocal
FROM pg_catalog.pg_constraint ct
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=cl.relnamespace
JOIN pg_catalog.pg_class cr ON cr.oid=confrelid
JOIN pg_catalog.pg_namespace nr ON nr.oid=cr.relnamespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=ct.oid AND des.classoid='pg_constraint'::regclass)
WHERE contype='f' AND
conrelid = {{tid}}::oid
{% if fkid %}
AND ct.oid = {{fkid}}::oid
{% endif %}
ORDER BY conname
2,根据上面的查询结果，查询主表信息
{% for keypair in keys %}
{% if loop.index != 1 %}
UNION
{% endif %}
SELECT a1.attname as conattname,
a2.attname as confattname
FROM pg_catalog.pg_attribute a1,
pg_catalog.pg_attribute a2
WHERE a1.attrelid={{tid}}::oid
AND a1.attnum={{keypair[1]}}
AND a2.attrelid={{confrelid}}::oid
AND a2.attnum={{keypair[0]}}
{% endfor %}
实际执行：
SELECT ct.oid,
conname as name,
condeferrable,
condeferred,
confupdtype,
confdeltype,
CASE confmatchtype
WHEN 's' THEN FALSE
WHEN 'f' THEN TRUE
END AS confmatchtype,
conkey,
confkey,
confrelid,
nl.nspname as fknsp,
cl.relname as fktab,
nr.oid as refnspoid,
nr.nspname as refnsp,
cr.relname as reftab,
description as comment,
convalidated,
conislocal
FROM pg_catalog.pg_constraint ct
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=cl.relnamespace
JOIN pg_catalog.pg_class cr ON cr.oid=confrelid
JOIN pg_catalog.pg_namespace nr ON nr.oid=cr.relnamespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=ct.oid AND des.classoid='pg_constraint'::regclass)
WHERE contype='f' AND
conrelid = 25280::oid
AND ct.oid = 25285::oid
ORDER BY conname
2,
SELECT a1.attname as conattname,
a2.attname as confattname
FROM pg_catalog.pg_attribute a1,
pg_catalog.pg_attribute a2
WHERE a1.attrelid=25280::oid
AND a1.attnum=3
AND a2.attrelid=25273::oid
AND a2.attnum=1
3，唯一约束属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24580
cid=24620
constraint_type='u' # 查询唯一约束
pg模板：
SELECT cls.oid,
cls.relname as name,
indnatts as col_count,
CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END as spcname,
CASE contype
WHEN 'p' THEN desp.description
WHEN 'u' THEN desp.description
WHEN 'x' THEN desp.description
ELSE des.description
END AS comment,
condeferrable,
condeferred,
conislocal,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = {{tid}}::oid
{% if cid %}
AND cls.oid = {{cid}}::oid
{% endif %}
AND contype='{{constraint_type}}'
ORDER BY cls.relname
{###
We need outer SELECT & dummy column to preserve the ordering
because we lose ordering when we use UNION
###}
SELECT * FROM (
{% for n in range(colcnt|int) %}
{% if loop.index != 1 %}
UNION SELECT pg_catalog.pg_get_indexdef({{ cid|string }}, {{ loop.index|string }}, true) AS column, {{ n }} AS dummy
{% else %}
SELECT pg_catalog.pg_get_indexdef({{ cid|string }} , {{ loop.index|string }} , true) AS column, {{ n }} AS dummy
{% endif %}
{% endfor %}
) tmp
ORDER BY dummy
实际执行：
SELECT cls.oid,
cls.relname as name,
indnkeyatts as col_count,
CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 13799::oid)
END as spcname,
CASE contype
WHEN 'p' THEN desp.description
WHEN 'u' THEN desp.description
WHEN 'x' THEN desp.description
ELSE des.description
END AS comment,
condeferrable,
condeferred,
conislocal,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = 24580::oid
AND cls.oid = 24626::oid
AND contype='u'
ORDER BY cls.relname
SELECT * FROM (
SELECT pg_catalog.pg_get_indexdef(24626 , 1 , true) AS column, 0 AS dummy
) tmp
ORDER BY dummy
4，检查约束属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
cid=24628
constraint_type='c' # 查询检查约束
pg模板：
SELECT c.oid, conname as name, relname, nspname, description as comment,
pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc,
connoinherit, NOT convalidated as convalidated, conislocal
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=relnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND
des.classoid='pg_constraint'::regclass)
WHERE contype = 'c'
AND conrelid = {{ tid }}::oid
{% if cid %}
AND c.oid = {{ cid }}::oid
{% endif %}
实际执行：
SELECT c.oid, conname as name, relname, nspname, description as comment,
pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc,
connoinherit, NOT convalidated as convalidated, conislocal
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=relnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND
des.classoid='pg_constraint'::regclass)
WHERE contype = 'c'
AND conrelid = 24580::oid
AND c.oid = 24628::oid
5，排他约束属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=25264
exid=25269
pg模板：
# 1，查询基本信息
SELECT cls.oid,
cls.relname as name,
indnatts as col_count,
amname,
CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = {{ did }}::oid)
END as spcname,
CASE contype
WHEN 'p' THEN desp.description
WHEN 'u' THEN desp.description
WHEN 'x' THEN desp.description
ELSE des.description
END AS comment,
condeferrable,
condeferred,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor,
pg_catalog.pg_get_expr(idx.indpred, idx.indrelid, true) AS indconstraint
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = {{tid}}::oid
{% if cid %}
AND cls.oid = {{cid}}::oid
{% endif %}
AND contype='x'
ORDER BY cls.relname
# 2,查询列相关信息
{% for n in range(colcnt|int) %}
{% if loop.index != 1 %}
UNION
{% endif %}
SELECT
i.indoption[{{loop.index -1}}] AS options,
pg_catalog.pg_get_indexdef(i.indexrelid, {{loop.index}}, true) AS coldef,
op.oprname,
CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname
,
coll.collname,
nspc.nspname as collnspname,
pg_catalog.format_type(ty.oid,NULL) AS datatype,
CASE WHEN pg_catalog.pg_get_indexdef(i.indexrelid, {{loop.index}}, true) = a.attname THEN FALSE ELSE TRUE END AS is_exp
FROM pg_catalog.pg_index i
JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = {{loop.index}})
JOIN pg_catalog.pg_type ty ON ty.oid=a.atttypid
LEFT OUTER JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[{{loop.index -1}}])
LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid) LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[{{loop.index}}])
LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
WHERE i.indexrelid = {{cid}}::oid
{% endfor %}
实际执行：
SELECT cls.oid,
cls.relname as name,
indnkeyatts as col_count,
amname,
CASE WHEN length(spcname::text) > 0 THEN spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = 13799::oid)
END as spcname,
CASE contype
WHEN 'p' THEN desp.description
WHEN 'u' THEN desp.description
WHEN 'x' THEN desp.description
ELSE des.description
END AS comment,
condeferrable,
condeferred,
substring(pg_catalog.array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor,
pg_catalog.pg_get_expr(idx.indpred, idx.indrelid, true) AS indconstraint
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
JOIN pg_catalog.pg_am am ON am.oid=cls.relam
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
WHERE indrelid = 25264::oid
AND cls.oid = 25269::oid
AND contype='x'
ORDER BY cls.relname
SELECT
i.indoption[0] AS options,
pg_catalog.pg_get_indexdef(i.indexrelid, 1, true) AS coldef,
op.oprname,
CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname
,
coll.collname,
nspc.nspname as collnspname,
pg_catalog.format_type(ty.oid,NULL) AS datatype,
CASE WHEN pg_catalog.pg_get_indexdef(i.indexrelid, 1, true) = a.attname THEN FALSE ELSE TRUE END AS is_exp
FROM pg_catalog.pg_index i
JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = 1)
JOIN pg_catalog.pg_type ty ON ty.oid=a.atttypid
LEFT OUTER JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[0])
LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid) LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[1])
LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
WHERE i.indexrelid = 25269::oid
UNION
SELECT
i.indoption[1] AS options,
pg_catalog.pg_get_indexdef(i.indexrelid, 2, true) AS coldef,
op.oprname,
CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname
,
coll.collname,
nspc.nspname as collnspname,
pg_catalog.format_type(ty.oid,NULL) AS datatype,
CASE WHEN pg_catalog.pg_get_indexdef(i.indexrelid, 2, true) = a.attname THEN FALSE ELSE TRUE END AS is_exp
FROM pg_catalog.pg_index i
JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = 2)
JOIN pg_catalog.pg_type ty ON ty.oid=a.atttypid
LEFT OUTER JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[1])
LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid) LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[2])
LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
WHERE i.indexrelid = 25269::oid
（四）删除
1，删除主键约束
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
cid=24618
pg模板：
SELECT cls.relname as name
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND
dep.objid = cls.oid AND
dep.refobjsubid = '0'
AND dep.refclassid=(SELECT oid
FROM pg_catalog.pg_class
WHERE relname='pg_constraint') AND
dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND
con.oid = dep.refobjid)
WHERE indrelid = {{tid}}::oid
AND contype='{{constraint_type}}'
AND cls.oid = {{cid}}::oid;
{% if data %}
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.schema, data.table) }} DROP CONSTRAINT IF EXISTS {{ conn|qtIdent(data.name) }}{% if cascade%} CASCADE{% endif %};
{% endif %}
实际执行：先查再删
SELECT cls.relname as name
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND
dep.objid = cls.oid AND
dep.refobjsubid = '0'
AND dep.refclassid=(SELECT oid
FROM pg_catalog.pg_class
WHERE relname='pg_constraint') AND
dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND
con.oid = dep.refobjid)
WHERE indrelid = 24580::oid
AND contype='p'
AND cls.oid = 24618::oid;
ALTER TABLE IF EXISTS public.student DROP CONSTRAINT IF EXISTS student_pk;
2，删除外键约束
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24580
fkid=25285
pg模板：
1,
SELECT conname as name
FROM pg_catalog.pg_constraint ct
WHERE ct.oid = {{fkid}}::oid
2,
{% if data %}
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.schema, data.table) }} DROP CONSTRAINT IF EXISTS {{ conn|qtIdent(data.name) }}{% if cascade%} CASCADE{% endif %};
{% endif %}
实际执行：先查再删：
1,
SELECT conname as name
FROM pg_catalog.pg_constraint ct
WHERE ct.oid = 25285::oid
2,
ALTER TABLE IF EXISTS public.department1 DROP CONSTRAINT IF EXISTS c_fk;
3，删除唯一约束
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
cid=24620
pg模板：
SELECT cls.relname as name
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND
dep.objid = cls.oid AND
dep.refobjsubid = '0'
AND dep.refclassid=(SELECT oid
FROM pg_catalog.pg_class
WHERE relname='pg_constraint') AND
dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND
con.oid = dep.refobjid)
WHERE indrelid = {{tid}}::oid
AND contype='{{constraint_type}}'
AND cls.oid = {{cid}}::oid;
{% if data %}
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.schema, data.table) }} DROP CONSTRAINT IF EXISTS {{ conn|qtIdent(data.name) }}{% if cascade%} CASCADE{% endif %};
{% endif %}
实际执行：
SELECT cls.relname as name
FROM pg_catalog.pg_index idx
JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND
dep.objid = cls.oid AND
dep.refobjsubid = '0'
AND dep.refclassid=(SELECT oid
FROM pg_catalog.pg_class
WHERE relname='pg_constraint') AND
dep.deptype='i')
LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid AND
con.oid = dep.refobjid)
WHERE indrelid = 24580::oid
AND contype='u'
AND cls.oid = 24626::oid;
ALTER TABLE IF EXISTS public.student DROP CONSTRAINT IF EXISTS student_uk;
4，删除检查约束
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
cid=25272
pg模板：
1，先获取基本信息
SELECT c.oid, conname as name, relname, nspname, description as comment,
pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc,
connoinherit, NOT convalidated as convalidated, conislocal
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=relnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND
des.classoid='pg_constraint'::regclass)
WHERE contype = 'c'
AND conrelid = {{ tid }}::oid
{% if cid %}
AND c.oid = {{ cid }}::oid
{% endif %}
2,再删除：
{% if data %}
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.nspname, data.relname) }} DROP CONSTRAINT IF EXISTS {{ conn|qtIdent(data.name) }};
{% endif %}
实际执行：先查再删：
SELECT c.oid, conname as name, relname, nspname, description as comment,
pg_catalog.pg_get_expr(conbin, conrelid, true) as consrc,
connoinherit, NOT convalidated as convalidated, conislocal
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class cl ON cl.oid=conrelid
JOIN pg_catalog.pg_namespace nl ON nl.oid=relnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND
des.classoid='pg_constraint'::regclass)
WHERE contype = 'c'
AND conrelid = 24580::oid
AND c.oid = 25272::oid
ALTER TABLE IF EXISTS public.student DROP CONSTRAINT IF EXISTS student_ck;
5，删除排他约束
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=25264
exid=25269
pg模板：
SELECT conname as name
FROM pg_catalog.pg_constraint ct
WHERE ct.conindid = {{exid}}::oid
{% if data %}
ALTER TABLE IF EXISTS {{ conn|qtIdent(data.schema, data.table) }} DROP CONSTRAINT IF EXISTS {{ conn|qtIdent(data.name) }}{% if cascade%} CASCADE{% endif %};
{% endif %}
实际执行：先查再删：
SELECT conname as name
FROM pg_catalog.pg_constraint ct
WHERE ct.conindid = 25269::oid
ALTER TABLE IF EXISTS public.t2 DROP CONSTRAINT IF EXISTS t2_ek;
二十三，规则
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24500
pg模板：
SELECT
rw.oid AS oid,
rw.rulename AS name,
CASE WHEN rw.ev_enabled != 'D' THEN True ELSE False END AS enabled,
rw.ev_enabled AS is_enable_rule
FROM
pg_catalog.pg_rewrite rw
WHERE
{% if tid %}
rw.ev_class = {{ tid }}
{% elif rid %}
rw.oid = {{ rid }}
{% endif %}
{% if schema_diff %}
AND CASE WHEN (SELECT COUNT(*) FROM pg_catalog.pg_depend
WHERE objid = rw.oid AND deptype = 'e') > 0 THEN FALSE ELSE TRUE END
{% endif %}
ORDER BY
rw.rulename
实际执行：
SELECT
rw.oid AS oid,
rw.rulename AS name,
CASE WHEN rw.ev_enabled != 'D' THEN True ELSE False END AS enabled,
rw.ev_enabled AS is_enable_rule
FROM
pg_catalog.pg_rewrite rw
WHERE
rw.ev_class = 24580
ORDER BY
rw.rulename
二十四，触发器
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24611
pg模板：
SELECT t.oid, t.tgname as name, t.tgenabled AS is_enable_trigger
FROM pg_catalog.pg_trigger t
WHERE NOT tgisinternal
AND tgrelid = {{tid}}::OID
{% if trid %}
AND t.oid = {{trid}}::OID
{% endif %}
{% if schema_diff %}
AND CASE WHEN (SELECT COUNT(*) FROM pg_catalog.pg_depend
WHERE objid = t.oid AND deptype = 'e') > 0 THEN FALSE ELSE TRUE END
{% endif %}
ORDER BY tgname;
实际执行：
SELECT t.oid, t.tgname as name, t.tgenabled AS is_enable_trigger
FROM pg_catalog.pg_trigger t
WHERE NOT tgisinternal
AND tgrelid = 24611::OID
ORDER BY tgname;
（二）创建
1，常规标签
2，定义标签
1，查询触发器函数：
SELECT pg_catalog.quote_ident(nspname) || '.' || pg_catalog.quote_ident(proname) AS tfunctions
FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n, pg_catalog.pg_language l
WHERE p.pronamespace = n.oid
AND p.prolang = l.oid
-- PGOID_TYPE_TRIGGER = 2279
AND prorettype = 2279
-- If Show SystemObjects is not true
AND (nspname NOT LIKE 'pg\_%' AND nspname NOT in ('information_schema'))
-- Find function for specific OID
ORDER BY nspname ASC, proname ASC
3，事件标签
4，转换标签
5，代码标签
6，点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24611
data={'description': '测试', 'evnt_insert': True, 'evnt_truncate': False, 'fires': 'BEFORE', 'is_row_trigger': True, 'name': 'update_order_status_trigger', 'tfunction': 'public.update_order_status'}
pg模板：
{### Set a flag which allows us to put OR between events ###}
{% set or_flag = False %}
{% if data.lanname == 'edbspl' or data.tfunction == 'Inline EDB-SPL' %}
CREATE OR REPLACE TRIGGER {{ conn|qtIdent(data.name) }}
{% else %}
CREATE{% if data.is_constraint_trigger %} CONSTRAINT{% endif %} TRIGGER {{ conn|qtIdent(data.name) }}
{% endif %}
{{data.fires}} {% if data.evnt_insert %}INSERT{% set or_flag = True %}
{% endif %}{% if data.evnt_delete %}
{% if or_flag %} OR {% endif %}DELETE{% set or_flag = True %}
{% endif %}{% if data.evnt_truncate %}
{% if or_flag %} OR {% endif %}TRUNCATE{% set or_flag = True %}
{% endif %}{% if data.evnt_update %}
{% if or_flag %} OR {% endif %}UPDATE {% if data.columns|length > 0 %}OF {% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{ conn|qtIdent(c) }}{% endfor %}{% endif %}
{% endif %}
ON {{ conn|qtIdent(data.schema, data.table) }}
{% if data.tgdeferrable %}
DEFERRABLE{% if data.tginitdeferred %} INITIALLY DEFERRED{% endif %}
{% endif %}
{% if data.tgoldtable or data.tgnewtable %}
REFERENCING{% if data.tgnewtable %} NEW TABLE AS {{ conn|qtIdent(data.tgnewtable) }}{% endif %}{% if data.tgoldtable %} OLD TABLE AS {{ conn|qtIdent(data.tgoldtable) }}{% endif %}
{% endif %}
FOR EACH{% if data.is_row_trigger %} ROW{% else %} STATEMENT{% endif %}
{% if data.whenclause %}
WHEN {% if not data.oid %}({% endif %}{{ data.whenclause }}{% if not data.oid %}){% endif %}{% endif %}
{% if data.prosrc is defined and
(data.lanname == 'edbspl' or data.tfunction == 'Inline EDB-SPL') %}{{ data.prosrc }}{% else %}EXECUTE FUNCTION {{ data.tfunction }}{% if data.tgargs %}({{ data.tgargs }}){% else %}(){% endif%}{% endif%};
{% if data.description %}
COMMENT ON TRIGGER {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }}
IS {{data.description|qtLiteral}};
{% endif %}
实际执行：
CREATE TRIGGER update_order_status_trigger
BEFORE INSERT
ON public.orders
FOR EACH ROW
EXECUTE FUNCTION public.update_order_status();
COMMENT ON TRIGGER update_order_status_trigger ON public.orders
IS '测试';
再获取时间触发器的OID并连同type等信息返回给前端：
SELECT t.oid
FROM pg_catalog.pg_trigger t
WHERE NOT tgisinternal
AND tgrelid = 24611::OID
AND tgname = 'update_order_status_trigger';
（三）属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24611
trid=24617
pg模板：
SELECT t.oid,t.tgname AS name, t.xmin, t.tgenabled AS is_enable_trigger, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
COALESCE(pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) EXECUTE (PROCEDURE|FUNCTION)'),
pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) \$trigger')) AS whenclause,
-- We need to convert tgargs column bytea datatype to array datatype
(pg_catalog.string_to_array(encode(tgargs, 'escape'), E'\\000')::text[])[1:tgnargs] AS custom_tgargs,
{% if datlastsysoid %}
(CASE WHEN t.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_trigger,
{% endif %}
(CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constraint_trigger,
tgoldtable,
tgnewtable
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class cl ON cl.oid=tgrelid
JOIN pg_catalog.pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
LEFT OUTER JOIN pg_catalog.pg_proc p ON p.oid=t.tgfoid
LEFT OUTER JOIN pg_catalog.pg_language l ON l.oid=p.prolang
WHERE NOT tgisinternal
AND tgrelid = {{tid}}::OID
{% if trid %}
AND t.oid = {{trid}}::OID
{% endif %}
ORDER BY tgname;
实际执行：
SELECT t.oid,t.tgname AS name, t.xmin, t.tgenabled AS is_enable_trigger, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
COALESCE(pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) EXECUTE (PROCEDURE|FUNCTION)'),
pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) \$trigger')) AS whenclause,
-- We need to convert tgargs column bytea datatype to array datatype
(pg_catalog.string_to_array(encode(tgargs, 'escape'), E'\\000')::text[])[1:tgnargs] AS custom_tgargs,
(CASE WHEN t.oid <= 16383::oid THEN true ElSE false END) AS is_sys_trigger,
(CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constraint_trigger,
tgoldtable,
tgnewtable
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class cl ON cl.oid=tgrelid
JOIN pg_catalog.pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
LEFT OUTER JOIN pg_catalog.pg_proc p ON p.oid=t.tgfoid
LEFT OUTER JOIN pg_catalog.pg_language l ON l.oid=p.prolang
WHERE NOT tgisinternal
AND tgrelid = 24611::OID
AND t.oid = 24617::OID
ORDER BY tgname;
（四）删除
请求参数：
gid=1
sid=1
did=13799
scid=2200
tid=24611
trid=24617
pg模板：
# 1,
SELECT t.oid,t.tgname AS name, t.xmin, t.tgenabled AS is_enable_trigger, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
COALESCE(pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) EXECUTE (PROCEDURE|FUNCTION)'),
pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) \$trigger')) AS whenclause,
-- We need to convert tgargs column bytea datatype to array datatype
(pg_catalog.string_to_array(encode(tgargs, 'escape'), E'\\000')::text[])[1:tgnargs] AS custom_tgargs,
{% if datlastsysoid %}
(CASE WHEN t.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_trigger,
{% endif %}
(CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constraint_trigger,
tgoldtable,
tgnewtable
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class cl ON cl.oid=tgrelid
JOIN pg_catalog.pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
LEFT OUTER JOIN pg_catalog.pg_proc p ON p.oid=t.tgfoid
LEFT OUTER JOIN pg_catalog.pg_language l ON l.oid=p.prolang
WHERE NOT tgisinternal
AND tgrelid = {{tid}}::OID
{% if trid %}
AND t.oid = {{trid}}::OID
{% endif %}
ORDER BY tgname;
# 2,
DROP TRIGGER IF EXISTS {{conn|qtIdent(data.name)}} ON {{conn|qtIdent(data.nspname, data.relname )}}{% if cascade %} CASCADE{% endif %};
实际执行：同样是先根据trid查询出触发器名称，再根据触发器名称删除触发器：
SELECT t.oid,t.tgname AS name, t.xmin, t.tgenabled AS is_enable_trigger, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
COALESCE(pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) EXECUTE (PROCEDURE|FUNCTION)'),
pg_catalog.substring(pg_catalog.pg_get_triggerdef(t.oid, true), 'WHEN (.*) \$trigger')) AS whenclause,
-- We need to convert tgargs column bytea datatype to array datatype
(pg_catalog.string_to_array(encode(tgargs, 'escape'), E'\\000')::text[])[1:tgnargs] AS custom_tgargs,
(CASE WHEN t.oid <= 16383::oid THEN true ElSE false END) AS is_sys_trigger,
(CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constraint_trigger,
tgoldtable,
tgnewtable
FROM pg_catalog.pg_trigger t
JOIN pg_catalog.pg_class cl ON cl.oid=tgrelid
JOIN pg_catalog.pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
LEFT OUTER JOIN pg_catalog.pg_proc p ON p.oid=t.tgfoid
LEFT OUTER JOIN pg_catalog.pg_language l ON l.oid=p.prolang
WHERE NOT tgisinternal
AND tgrelid = 24611::OID
AND t.oid = 24617::OID
ORDER BY tgname;
DROP TRIGGER IF EXISTS update_order_status_trigger ON public.orders;
二十五，序列
（一）查询
请求参数：
gid=1
sid=1
scid=2200
pg模板：
SELECT cl.oid as oid, relname as name, relnamespace as schema
FROM pg_catalog.pg_class cl
{% if show_internal %}
LEFT JOIN pg_catalog.pg_depend d1 ON d1.refobjid = cl.oid AND d1.deptype = 'i'
{% endif %}
WHERE
relkind = 'S'
{% if scid %}
AND relnamespace = {{scid|qtLiteral}}::oid
{% endif %}
{% if seid %}
AND cl.oid = {{seid|qtLiteral}}::oid
{% endif %}
{% if schema_diff %}
AND CASE WHEN (SELECT COUNT(*) FROM pg_catalog.pg_depend
WHERE objid = cl.oid AND deptype = 'e') > 0 THEN FALSE ELSE TRUE END
{% endif %}
ORDER BY relname
实际执行：
SELECT cl.oid as oid, relname as name, relnamespace as schema
FROM pg_catalog.pg_class cl
WHERE
relkind = 'S'
AND relnamespace = 2200::oid
ORDER BY relname
（二）创建
1，常规标签
2，定义标签
3，安全标签
4，点击保存
请求参数：
gid=1
sid=1
scid=2200
data={'name': 'test', 'seqowner': 'postgres', 'schema': 'public', 'comment': '测试', 'increment': '1', 'start': '1', 'minimum': '1', 'maximum': '9223372036854775807', 'cache': '1', 'cycled': True, 'relacl': [], 'securities': [], 'owned_table': 'student', 'owned_column': 'sid'}
pg模板：
1，create-ddl:
CREATE SEQUENCE{% if add_not_exists_clause %} IF NOT EXISTS{% endif %} {{ conn|qtIdent(data.schema, data.name) }}{% if data.increment is defined and data.cycled %}
CYCLE{% endif %}{% if data.increment is defined %}
INCREMENT {{data.increment|int}}{% endif %}{% if data.start is defined %}
START {{data.start|int}}{% elif data.current_value is defined %}
START {{data.current_value|int}}{% endif %}{% if data.minimum is defined %}
MINVALUE {{data.minimum|int}}{% endif %}{% if data.maximum is defined %}
MAXVALUE {{data.maximum|int}}{% endif %}{% if data.cache is defined and data.cache|int(-1) > -1%}
CACHE {{data.cache|int}}{% endif %}{% if data.owned_table is defined and data.owned_table != None and data.owned_column is defined and data.owned_column != None %}
OWNED BY {{ conn|qtIdent(data.owned_table) }}.{{ conn|qtIdent(data.owned_column) }}{% endif %};
2，grant:
{% import 'macros/schemas/security.macros' as SECLABEL %}
{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
{# Construct sequence name from name and schema #}
{% set seqname=conn|qtIdent(data.schema, data.name) %}
{% if data.seqowner %}
ALTER SEQUENCE {{ seqname }}
OWNER TO {{ conn|qtIdent(data.seqowner) }};
{% endif %}
{% if data.comment %}
COMMENT ON SEQUENCE {{ seqname }}
IS {{ data.comment|qtLiteral }};
{% endif %}
{% if data.securities %}
{% for r in data.securities %}
{{ SECLABEL.SET(conn, 'SEQUENCE', data.name, r.provider, r.label, data.schema) }}
{% endfor %}
{% endif %}
{% if data.relacl %}
{% for priv in data.relacl %}
{% if priv.without_grant %}
GRANT {{ priv.without_grant|join(', ') }} ON 'SEQUENCE' {{ conn|qtIdent(data.schema, data.name) }} TO priv.grantee;
{% endif %}
{% if priv.with_grant %}
{% if priv.without_grant %}
{# This empty if is to add new line in between #}
{% endif %}
GRANT {{ priv.with_grant|join(', ') }} ON 'SEQUENCE' {{ conn|qtIdent(data.schema, data.name) }} TO priv.grantee WITH GRANT OPTION;
{% endif %}
{%- endmacro %}
{% endfor %}
{% endif %}
实际执行：
CREATE SEQUENCE public.test
CYCLE
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1
OWNED BY student.sid;
ALTER SEQUENCE public.test
OWNER TO postgres;
COMMENT ON SEQUENCE public.test
IS '测试';
创建后执行下面语句获取oid:
SELECT cl.oid as oid, relnamespace
FROM pg_catalog.pg_class cl
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cl.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON (nsp.oid = cl.relnamespace)
WHERE relkind = 'S'
AND relname = 'test'
AND nspname = 'public'
SELECT cl.oid as oid, relnamespace
FROM pg_catalog.pg_class cl
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cl.oid AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON (nsp.oid = cl.relnamespace)
WHERE relkind = 'S'
AND relname = {{ name|qtLiteral }}
AND nspname = {{ schema|qtLiteral }}
（三）属性
请求参数：
gid=1
sid=1
scid=2200
seid=24603
pg模板：
获取基础信息：
{% if scid %}
SELECT
cl.oid as oid,
cl.relname as name,
nsp.nspname as schema,
pg_catalog.pg_get_userbyid(cl.relowner) AS seqowner,
description as comment,
pg_catalog.array_to_string(cl.relacl::text[], ', ') as acl,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=cl.oid) AS securities,
depcl.relname AS owned_table,
att.attname AS owned_column
FROM pg_catalog.pg_class cl
LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON cl.relnamespace = nsp.oid
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cl.oid
AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_depend dep ON (dep.objid=cl.oid and deptype = 'a')
LEFT JOIN pg_catalog.pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
LEFT JOIN pg_catalog.pg_class depcl ON depcl.oid = att.attrelid
WHERE cl.relkind = 'S' AND cl.relnamespace = {{scid}}::oid
{% if seid %}AND cl.oid = {{seid}}::oid {% endif %}
ORDER BY cl.relname
{% endif %}
查询数值信息：
SELECT
last_value,
seqmin AS min_value,
seqmax AS max_value,
seqstart AS start_value,
seqcache AS cache_value,
seqcycle AS is_cycled,
seqincrement AS increment_by,
is_called
FROM pg_catalog.pg_sequence, {{ conn|qtIdent(data.schemaname) }}.{{ conn|qtIdent(data.sequencename) }}
WHERE seqrelid = {{seid}}
获取acl信息：
SELECT 'relacl' as deftype, COALESCE(gt.rolname, 'PUBLIC') grantee, g.rolname grantor, pg_catalog.array_agg(privilege_type) as privileges, pg_catalog.array_agg(is_grantable) as grantable
FROM
(SELECT
d.grantee, d.grantor, d.is_grantable,
CASE d.privilege_type
WHEN 'CONNECT' THEN 'c'
WHEN 'CREATE' THEN 'C'
WHEN 'DELETE' THEN 'd'
WHEN 'EXECUTE' THEN 'X'
WHEN 'INSERT' THEN 'a'
WHEN 'REFERENCES' THEN 'x'
WHEN 'SELECT' THEN 'r'
WHEN 'TEMPORARY' THEN 'T'
WHEN 'TRIGGER' THEN 't'
WHEN 'TRUNCATE' THEN 'D'
WHEN 'UPDATE' THEN 'w'
WHEN 'USAGE' THEN 'U'
ELSE 'UNKNOWN'
END AS privilege_type
FROM
pg_catalog.aclexplode((SELECT relacl
FROM pg_catalog.pg_class cl
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cl.oid AND des.classoid='pg_class'::regclass)
WHERE relkind = 'S' AND relnamespace = {{scid}}::oid
AND cl.oid = {{seid}}::oid )) d
) d
LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
GROUP BY g.rolname, gt.rolname
ORDER BY grantee
实际执行：
SELECT
cl.oid as oid,
cl.relname as name,
nsp.nspname as schema,
pg_catalog.pg_get_userbyid(cl.relowner) AS seqowner,
description as comment,
pg_catalog.array_to_string(cl.relacl::text[], ', ') as acl,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=cl.oid) AS securities,
depcl.relname AS owned_table,
att.attname AS owned_column
FROM pg_catalog.pg_class cl
LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON cl.relnamespace = nsp.oid
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cl.oid
AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_depend dep ON (dep.objid=cl.oid and deptype = 'a')
LEFT JOIN pg_catalog.pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
LEFT JOIN pg_catalog.pg_class depcl ON depcl.oid = att.attrelid
WHERE cl.relkind = 'S' AND cl.relnamespace = 2200::oid
AND cl.oid = 24603::oid ORDER BY cl.relname
SELECT
last_value,
seqmin AS min_value,
seqmax AS max_value,
seqstart AS start_value,
seqcache AS cache_value,
seqcycle AS is_cycled,
seqincrement AS increment_by,
is_called
FROM pg_catalog.pg_sequence, public.test5
WHERE seqrelid = 92407
SELECT 'relacl' as deftype, COALESCE(gt.rolname, 'PUBLIC') grantee, g.rolname grantor, pg_catalog.array_agg(privilege_type) as privileges, pg_catalog.array_agg(is_grantable) as grantable
FROM
(SELECT
d.grantee, d.grantor, d.is_grantable,
CASE d.privilege_type
WHEN 'CONNECT' THEN 'c'
WHEN 'CREATE' THEN 'C'
WHEN 'DELETE' THEN 'd'
WHEN 'EXECUTE' THEN 'X'
WHEN 'INSERT' THEN 'a'
WHEN 'REFERENCES' THEN 'x'
WHEN 'SELECT' THEN 'r'
WHEN 'TEMPORARY' THEN 'T'
WHEN 'TRIGGER' THEN 't'
WHEN 'TRUNCATE' THEN 'D'
WHEN 'UPDATE' THEN 'w'
WHEN 'USAGE' THEN 'U'
ELSE 'UNKNOWN'
END AS privilege_type
FROM
pg_catalog.aclexplode((SELECT relacl
FROM pg_catalog.pg_class cl
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cl.oid AND des.classoid='pg_class'::regclass)
WHERE relkind = 'S' AND relnamespace = 2200::oid
AND cl.oid = 24603::oid )) d
) d
LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
GROUP BY g.rolname, gt.rolname
ORDER BY grantee
（四）删除
请求参数：
gid=1
sid=1
scid=2200
seid=24603
data={'ids': [24603]}
pg模板：
DROP SEQUENCE IF EXISTS {{ conn|qtIdent(data.schema) }}.{{ conn|qtIdent(data.name) }}{% if cascade%} CASCADE{% endif %};
实际执行：先根据seid查出name等信息，再删除：
SELECT
cl.oid as oid,
cl.relname as name,
nsp.nspname as schema,
pg_catalog.pg_get_userbyid(cl.relowner) AS seqowner,
description as comment,
pg_catalog.array_to_string(cl.relacl::text[], ', ') as acl,
(SELECT pg_catalog.array_agg(provider || '=' || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=cl.oid) AS securities,
depcl.relname AS owned_table,
att.attname AS owned_column
FROM pg_catalog.pg_class cl
LEFT OUTER JOIN pg_catalog.pg_namespace nsp ON cl.relnamespace = nsp.oid
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cl.oid
AND des.classoid='pg_class'::regclass)
LEFT OUTER JOIN pg_catalog.pg_depend dep ON (dep.objid=cl.oid and deptype = 'a')
LEFT JOIN pg_catalog.pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
LEFT JOIN pg_catalog.pg_class depcl ON depcl.oid = att.attrelid
WHERE cl.relkind = 'S' AND cl.relnamespace = 2200::oid
AND cl.oid = 24603::oid ORDER BY cl.relname
DROP SEQUENCE IF EXISTS public.test;
二十六，排序规则
（一）查询
请求参数：
gid=1
sid=1
scid=2200
pg模板：
SELECT c.oid, c.collname AS name
FROM pg_catalog.pg_collation c
{% if scid %}
WHERE c.collnamespace = {{scid}}::oid
{% elif coid %}
WHERE c.oid = {{coid}}::oid
{% endif %}
{% if schema_diff %}
AND CASE WHEN (SELECT COUNT(*) FROM pg_catalog.pg_depend
WHERE objid = c.oid AND deptype = 'e') > 0 THEN FALSE ELSE TRUE END
{% endif %}
ORDER BY c.collname;
实际执行：
SELECT c.oid, c.collname AS name
FROM pg_catalog.pg_collation c
WHERE c.collnamespace = 2200::oid
ORDER BY c.collname;
二十七，域
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
pg模板：
SELECT
d.oid, d.typname as name, pg_catalog.pg_get_userbyid(d.typowner) as owner,
bn.nspname as basensp
FROM
pg_catalog.pg_type d
JOIN
pg_catalog.pg_type b ON b.oid = d.typbasetype
JOIN
pg_catalog.pg_namespace bn ON bn.oid=d.typnamespace
{% if scid is defined %}
WHERE
d.typnamespace = {{scid}}::oid
{% elif doid %}
WHERE d.oid = {{doid}}::oid
{% endif %}
{% if schema_diff %}
AND CASE WHEN (SELECT COUNT(*) FROM pg_catalog.pg_depend
WHERE objid = d.oid AND deptype = 'e') > 0 THEN FALSE ELSE TRUE END
{% endif %}
ORDER BY
d.typname;
实际执行：
SELECT
d.oid, d.typname as name, pg_catalog.pg_get_userbyid(d.typowner) as owner,
bn.nspname as basensp
FROM
pg_catalog.pg_type d
JOIN
pg_catalog.pg_type b ON b.oid = d.typbasetype
JOIN
pg_catalog.pg_namespace bn ON bn.oid=d.typnamespace
WHERE
d.typnamespace = 2200::oid
ORDER BY
d.typname;
（二）创建
1，常规标签
2，定义标签
3，约束标签
4，安全标签
5，点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
data={'basensp': 'public', 'basetype': 'integer', 'constraints': [{'conname': 'posint_check', 'consrc': 'VALUE > 0', 'convalidated': True}], 'name': 'posint', 'owner': 'postgres', 'schema': 'public', 'seclabels': [], 'typnotnull': True}
pg模板：
{% import 'macros/schemas/security.macros' as SECLABEL %}
{% if data %}
CREATE DOMAIN {{ conn|qtIdent(data.basensp, data.name) }}
AS {{ conn|qtTypeIdent(data.basetype) }}{% if data.typlen %}({{data.typlen}}{% if data.precision %},{{data.precision}}{% endif %}){% endif %}{% if data.collname and data.collname != "pg_catalog.\"default\"" %}
COLLATE {{ data.collname }}{% endif %}{% if data.typdefault %}
DEFAULT {{ data.typdefault }}{% endif %}{% if data.typnotnull %}
NOT NULL{% endif %};
{% if data.owner %}
ALTER DOMAIN {{ conn|qtIdent(data.basensp, data.name) }} OWNER TO {{ conn|qtIdent(data.owner) }};{% endif %}
{% if data.constraints %}
{% for c in data.constraints %}{% if c.conname and c.consrc %}
ALTER DOMAIN {{ conn|qtIdent(data.basensp, data.name) }}
ADD CONSTRAINT {{ conn|qtIdent(c.conname) }} CHECK ({{ c.consrc }}){% if not c.convalidated %} NOT VALID{% endif %}{% endif -%};
{% if c.description %}
COMMENT ON CONSTRAINT {{ conn|qtIdent(c.conname) }} ON DOMAIN {{ conn|qtIdent(data.basensp, data.name) }}
IS '{{ c.description }}';
{% endif %}
{% endfor -%}
{% endif %}
{% if data.description %}
COMMENT ON DOMAIN {{ conn|qtIdent(data.basensp, data.name) }}
IS '{{ data.description }}';{% endif -%}
{% if data.seclabels %}
{% for r in data.seclabels %}
{% if r.label and r.provider %}
SECURITY LABEL{% if r.provider and r.provider != '' %} FOR {{ conn|qtIdent(r.provider) }}{% endif %} ON 'DOMAIN' {{ conn|qtIdent(data.basensp, data.name) }} IS {{ r.label|qtLiteral }};
{% endfor -%}
{% endif -%}
{% endif -%}
实际执行：
CREATE DOMAIN public.posint
AS integer
NOT NULL;
ALTER DOMAIN public.posint OWNER TO postgres;
ALTER DOMAIN public.posint
ADD CONSTRAINT posint_check CHECK (VALUE > 0);
（三）属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
doid=24600
pg模板：
SELECT
d.oid, d.typname as name, d.typbasetype, pg_catalog.format_type(b.oid,NULL) as basetype, pg_catalog.pg_get_userbyid(d.typowner) as owner,
c.oid AS colloid, pg_catalog.format_type(b.oid, d.typtypmod) AS fulltype,
CASE WHEN length(cn.nspname::text) > 0 AND length(c.collname::text) > 0 THEN
pg_catalog.concat(cn.nspname, '."', c.collname,'"')
ELSE '' END AS collname,
d.typtypmod, d.typnotnull, d.typdefault, d.typndims, d.typdelim, bn.nspname as basensp,
description, (SELECT COUNT(1) FROM pg_catalog.pg_type t2 WHERE t2.typname=d.typname) > 1 AS domisdup,
(SELECT COUNT(1) FROM pg_catalog.pg_type t3 WHERE t3.typname=b.typname) > 1 AS baseisdup,
(SELECT
pg_catalog.array_agg(provider || '=' || label)
FROM
pg_catalog.pg_seclabel sl1
WHERE
sl1.objoid=d.oid) AS seclabels
FROM
pg_catalog.pg_type d
JOIN
pg_catalog.pg_type b ON b.oid = d.typbasetype
JOIN
pg_catalog.pg_namespace bn ON bn.oid=d.typnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=d.oid AND des.classoid='pg_type'::regclass)
LEFT OUTER JOIN
pg_catalog.pg_collation c ON d.typcollation=c.oid
LEFT OUTER JOIN
pg_catalog.pg_namespace cn ON c.collnamespace=cn.oid
WHERE
d.typnamespace = {{scid}}::oid
{% if doid %}
AND d.oid={{doid}}::oid
{% endif %}
ORDER BY
d.typname;
实际执行：
SELECT
d.oid, d.typname as name, d.typbasetype, pg_catalog.format_type(b.oid,NULL) as basetype, pg_catalog.pg_get_userbyid(d.typowner) as owner,
c.oid AS colloid, pg_catalog.format_type(b.oid, d.typtypmod) AS fulltype,
CASE WHEN length(cn.nspname::text) > 0 AND length(c.collname::text) > 0 THEN
pg_catalog.concat(cn.nspname, '."', c.collname,'"')
ELSE '' END AS collname,
d.typtypmod, d.typnotnull, d.typdefault, d.typndims, d.typdelim, bn.nspname as basensp,
description, (SELECT COUNT(1) FROM pg_catalog.pg_type t2 WHERE t2.typname=d.typname) > 1 AS domisdup,
(SELECT COUNT(1) FROM pg_catalog.pg_type t3 WHERE t3.typname=b.typname) > 1 AS baseisdup,
(SELECT
pg_catalog.array_agg(provider || '=' || label)
FROM
pg_catalog.pg_seclabel sl1
WHERE
sl1.objoid=d.oid) AS seclabels
FROM
pg_catalog.pg_type d
JOIN
pg_catalog.pg_type b ON b.oid = d.typbasetype
JOIN
pg_catalog.pg_namespace bn ON bn.oid=d.typnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=d.oid AND des.classoid='pg_type'::regclass)
LEFT OUTER JOIN
pg_catalog.pg_collation c ON d.typcollation=c.oid
LEFT OUTER JOIN
pg_catalog.pg_namespace cn ON c.collnamespace=cn.oid
WHERE
d.typnamespace = 2200::oid
AND d.oid=24600::oid
ORDER BY
d.typname;
（四）删除
请求参数：
gid=1
sid=1
did=13799
scid=2200
doid=24600
data={'ids': [24600]}
pg模板：
{% if scid and doid %}
SELECT
d.typname as name, bn.nspname as basensp
FROM
pg_catalog.pg_type d
JOIN
pg_catalog.pg_namespace bn ON bn.oid=d.typnamespace
WHERE
d.typnamespace = {{scid}}::oid
AND
d.oid={{doid}}::oid;
{% endif %}
{% if name %}
DROP DOMAIN IF EXISTS {{ conn|qtIdent(basensp, name) }}{% if cascade %} CASCADE{% endif %};
{% endif %}
实际执行：
SELECT
d.typname as name, bn.nspname as basensp
FROM
pg_catalog.pg_type d
JOIN
pg_catalog.pg_namespace bn ON bn.oid=d.typnamespace
WHERE
d.typnamespace = 2200::oid
AND
d.oid=24600::oid;
DROP DOMAIN IF EXISTS public.posint;
二十八，域约束
（一）查询
请求参数：
gid=1
sid=1
did=13799
scid=2200
doid=24600
pg模板：
SELECT
c.oid, conname AS name, typname AS relname, nspname, description,
pg_catalog.regexp_replace(pg_catalog.pg_get_constraintdef(c.oid, true), E'CHECK \\((.*)\\).*', E'\\1') AS consrc,
connoinherit, convalidated, convalidated AS convalidated_p
FROM
pg_catalog.pg_constraint c
JOIN
pg_catalog.pg_type t ON t.oid=contypid
JOIN
pg_catalog.pg_namespace nl ON nl.oid=typnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND des.classoid='pg_constraint'::regclass)
{% if doid %}
WHERE
contype = 'c' AND contypid = {{doid}}::oid
{% if coid %}
AND c.oid = {{ coid }}
{% endif %}
{% elif coid %}
WHERE
c.oid = {{ coid }}
{% endif %}
实际执行：
SELECT
c.oid, conname AS name, typname AS relname, nspname, description,
pg_catalog.regexp_replace(pg_catalog.pg_get_constraintdef(c.oid, true), E'CHECK \\((.*)\\).*', E'\\1') AS consrc,
connoinherit, convalidated, convalidated AS convalidated_p
FROM
pg_catalog.pg_constraint c
JOIN
pg_catalog.pg_type t ON t.oid=contypid
JOIN
pg_catalog.pg_namespace nl ON nl.oid=typnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND des.classoid='pg_constraint'::regclass)
WHERE
contype = 'c' AND contypid = 24600::oid
（二）创建
1，常规标签
2，定义标签
3，点击保存
请求参数：
gid=1
sid=1
did=13799
scid=2200
doid=24600
data={'consrc': 'VALUE < 100', 'convalidated': True, 'name': 'posint_check2'}
pg模板：
{% if data and schema and domain %}
ALTER DOMAIN {{ conn|qtIdent(schema, domain) }}
ADD CONSTRAINT {{ conn|qtIdent(data.name) }} CHECK ({{ data.consrc }}){% if not data.convalidated %}
NOT VALID{% endif %};{% if data.description %}
COMMENT ON CONSTRAINT {{ conn|qtIdent(data.name) }} ON DOMAIN {{ conn|qtIdent(schema, domain) }}
IS '{{ data.description }}';{% endif %}
{% endif %}
实际执行：
ALTER DOMAIN public.posint
ADD CONSTRAINT posint_check2 CHECK (VALUE < 100);
（三）属性
请求参数：
gid=1
sid=1
did=13799
scid=2200
doid=24600
coid=24601
pg模板：
SELECT
c.oid, conname AS name, typname AS relname, nspname, description,
pg_catalog.regexp_replace(pg_catalog.pg_get_constraintdef(c.oid, true), E'CHECK \\((.*)\\).*', E'\\1') AS consrc,
connoinherit, convalidated, convalidated AS convalidated_p
FROM
pg_catalog.pg_constraint c
JOIN
pg_catalog.pg_type t ON t.oid=contypid
JOIN
pg_catalog.pg_namespace nl ON nl.oid=typnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND des.classoid='pg_constraint'::regclass)
{% if doid %}
WHERE
contype = 'c' AND contypid = {{doid}}::oid
{% if coid %}
AND c.oid = {{ coid }}
{% endif %}
{% elif coid %}
WHERE
c.oid = {{ coid }}
{% endif %}
实际执行：
SELECT
c.oid, conname AS name, typname AS relname, nspname, description,
pg_catalog.regexp_replace(pg_catalog.pg_get_constraintdef(c.oid, true), E'CHECK \\((.*)\\).*', E'\\1') AS consrc,
connoinherit, convalidated, convalidated AS convalidated_p
FROM
pg_catalog.pg_constraint c
JOIN
pg_catalog.pg_type t ON t.oid=contypid
JOIN
pg_catalog.pg_namespace nl ON nl.oid=typnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND des.classoid='pg_constraint'::regclass)
WHERE
contype = 'c' AND contypid = 24600::oid
AND c.oid = 24601
（四）删除
请求参数：
gid=1
sid=1
did=13799
scid=2200
doid=24600
coid=24602
data={'ids': [24602]}
pg模板：
{% if data %}
ALTER DOMAIN {{ conn|qtIdent(data.nspname, data.relname) }}
DROP CONSTRAINT {{ conn|qtIdent(data.name) }};
{% endif %}
实际执行：根据coid查出name，再根据查出得信息去删除域约束
SELECT
c.oid, conname AS name, typname AS relname, nspname, description,
pg_catalog.regexp_replace(pg_catalog.pg_get_constraintdef(c.oid, true), E'CHECK \\((.*)\\).*', E'\\1') AS consrc,
connoinherit, convalidated, convalidated AS convalidated_p
FROM
pg_catalog.pg_constraint c
JOIN
pg_catalog.pg_type t ON t.oid=contypid
JOIN
pg_catalog.pg_namespace nl ON nl.oid=typnamespace
LEFT OUTER JOIN
pg_catalog.pg_description des ON (des.objoid=c.oid AND des.classoid='pg_constraint'::regclass)
WHERE
contype = 'c' AND contypid = 24600::oid
AND c.oid = 24602
ALTER DOMAIN public.posint
DROP CONSTRAINT posint_check2;
----------------------------------------------------------------------------------
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（一）查询
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（二）创建
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（三）属性
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（四）删除
请求参数：
在这里插入代码片
pg模板：
在这里插入代码片
实际执行：
在这里插入代码片
（五）备份
（六）
（七）
（八）
（九）
（十）
（十一）
1，标签
2，标签
3，标签
4，标签
5，标签
6，
7，
8，
（1）
（2）
（3）
（4）
（5）
（6）
（7）
（8）
附录
（一）创建与使用事件触发器
准备测试对象：
1，创建数据表：
CREATE TABLE ddl_history (
id serial primary key,
ddl_date timestamptz,
ddl_tag text,
object_name text
);
2，创建事件触发器函数：
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
audit_query TEXT;
r RECORD;
BEGIN
IF tg_tag <> 'DROP TABLE'
THEN
r := pg_event_trigger_ddl_commands();
INSERT INTO ddl_history (ddl_date, ddl_tag, object_name)
VALUES (statement_timestamp(), tg_tag, r.object_identity);
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION log_ddl_drop()
RETURNS event_trigger AS $$
DECLARE
audit_query TEXT;
r RECORD;
BEGIN
IF tg_tag = 'DROP TABLE'
THEN
FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
INSERT INTO ddl_history (ddl_date, ddl_tag, object_name)
VALUES (statement_timestamp(), tg_tag, r.object_identity);
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;
3，创建事件触发器：
CREATE EVENT TRIGGER log_ddl_info ON ddl_command_end EXECUTE PROCEDURE log_ddl();
CREATE EVENT TRIGGER log_ddl_drop_info ON sql_drop EXECUTE PROCEDURE log_ddl_drop();
4，执行一些DDL操作：
postgres=# CREATE TABLE testtable (id int, first_name text);
CREATE TABLE
postgres=# ALTER TABLE testtable ADD COLUMN last_name text;
ALTER TABLE
postgres=# ALTER TABLE testtable ADD COLUMN midlname text;
ALTER TABLE
postgres=# ALTER TABLE testtable RENAME COLUMN midlname TO middle_name;
ALTER TABLE
postgres=# ALTER TABLE testtable DROP COLUMN middle_name;
ALTER TABLE
postgres=# DROP TABLE testtable;
DROP TABLE
postgres=# select * from ddl_history;
（二）创建与使用postgresql_fdw
1，在本地数据库创建postgresql_fwd扩展：
创建结果：
刷新“外部数据封装器”节点，得到：
2，在本地创建外部服务器：
创建完成后，就创建了远程服务器连接信息：
3，在本地创建用户映射：
创建完成后，就实现了本地用户到远程用户的映射：
4，在本地schema下创建外部表：
- 在定义列时，数据类型和其他属性必须与实际的远程表一致。
创建完成后，完成本地表对远程表的映射：
5，像操作本地表一样操作远程表
（三）使用视图
1，创建两个表：
CREATE TABLE teacher (
id int NOT NULL,
sname varchar(100)
);
CREATE TABLE student (
sid int NOT NULL,
teacher_id int NOT NULL DEFAULT 0,
tname varchar(100)
);
2，插入一些数据：
INSERT INTO public.teacher VALUES (1,'sname1');
INSERT INTO public.teacher VALUES (2,'sname2');
INSERT INTO public.teacher VALUES (3,'sname3');
INSERT INTO public.student VALUES (1,1,'tname1');
INSERT INTO public.student VALUES (2,1,'tname1');
INSERT INTO public.student VALUES (3,2,'tname2');
INSERT INTO public.student VALUES (4,3,'tname3');
INSERT INTO public.student VALUES (5,3,'tname3');
3，创建视图，从表中收集数据：
CREATE OR REPLACE VIEW student_view AS
SELECT *
FROM student
LEFT JOIN teacher
ON student.teacher_id = teacher.id;
4，使用视图：
SELECT * FROM student_view;
（四）使用物化视图
1，使用上面创建的表和数据。
2，创建物化视图：
CREATE MATERIALIZED VIEW student_view_m AS
SELECT *
FROM student
LEFT JOIN teacher
ON student.teacher_id = teacher.id;
3，向物化视图填充数据：
psql$ REFRESH MATERIALIZED VIEW
4，使用物化视图：
SELECT * FROM student_view_m;
（五）创建与使用触发器
1，创建一张表：
CREATE TABLE IF NOT EXISTS public.orders
(
id integer NOT NULL DEFAULT nextval('orders_id_seq'::regclass),
order_date date,
total_amount numeric(10,2),
status character varying(20) COLLATE pg_catalog."default",
CONSTRAINT orders_pkey PRIMARY KEY (id)
)
2，创建触发器函数：
CREATE OR REPLACE FUNCTION update_order_status()
RETURNS TRIGGER AS $update_order_status_trigger$
BEGIN
IF NEW.total_amount > 1000 THEN
NEW.status := '已审核';
ELSE
NEW.status := '待审核';
END IF;
RETURN NEW;
END;
$update_order_status_trigger$ LANGUAGE plpgsql;
3，创建触发器：
CREATE TRIGGER update_order_status_trigger
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_order_status();
4，向表中插入数据：
INSERT INTO orders (order_date, total_amount) VALUES ('2022-01-01', 1500);
INSERT INTO orders (order_date, total_amount) VALUES ('2022-01-02', 500);
5，查看表内容：
SELECT * FROM public.orders
ORDER BY id ASC
（六）表中的排他约束
1，创建一张表：
CREATE TABLE circles
(
c circle,
EXCLUDE USING gist (c WITH &&)
);
2，创建 btree_gist 扩展：
CREATE EXTENSION btree_gist
SCHEMA public
VERSION "1.6";
3，创建排他表：
CREATE TABLE t2
(
c1 INTEGER,
c2 TEXT,
EXCLUDE USING GIST (c1 WITH =, c2 WITH <>)
);
4，向表中插入数据：
# 第一次执行
insert into t2 values(1,'a');
# 第二次执行
insert into t2 values(1,'a');
# 第三次执行，回报错
ERROR: conflicting key value violates exclusion constraint "t2_c1_c2_excl"
DETAIL: Key (c1, c2)=(1, b) conflicts with existing key (c1, c2)=(1, a).
SQL 状态: 23P01
- 当 c1 列的值确定为 1 时，只能插入 c2 为 ’a’ 的值，插入其它数据则会报错。
（七）获取各类树节点 OID 的SQL
1，数据库 OID
SELECT oid FROM pg_database WHERE datname = '数据库名称';
2，获取所有登录/组角色 OID
SELECT oid, rolname FROM pg_roles
3，表空间
SELECT oid, spcname AS name FROM pg_tablespace;
4，publication
SELECT oid, pubname AS name
FROM pg_publication;
5，subscription
SELECT oid, subname AS name
FROM pg_subscription;
6，事件触发器
SELECT oid, evtname AS name
FROM pg_event_trigger;
7，外部服务器
SELECT oid, evtname AS name
FROM pg_event_trigger;
8，用户映射
SELECT umid, srvname AS name
FROM pg_user_mappings;
9，强制转换
SELECT castsource::regtype::oid AS source_oid,
casttarget::regtype::oid AS target_oid
FROM pg_cast;
SELECT castsource::regtype::oid AS source_oid,
casttarget::regtype::oid AS target_oid
FROM pg_cast
WHERE castsource = '源类型'::regtype
AND casttarget = '目标类型'::regtype;
10，扩展
SELECT oid, extname AS name
FROM pg_extension;
11，schema
SELECT oid, nspname AS name
FROM pg_namespace;
12，table
方式一：通过 schema OID 和 table 名称来查 table OID
SELECT rel.oid as tid
FROM pg_catalog.pg_class rel
WHERE rel.relkind IN ('r','s','t','p')
AND rel.relnamespace = {{ sci d }}::oid
AND rel.relname = {{ tableName }}
方式二：通过 schema 名称和 table 名称来查 table OID
SELECT * FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = {{ schemaName }}) AND relname = {{ tableName }};
13，sequence
根据名称查询sequence oid的SQL：
SELECT oid FROM pg_class WHERE relname = 'sequence_name' AND relkind = 'S';
14，触发器
根据触发器名称查询oid：
SELECT oid FROM pg_trigger WHERE tgname = 'trigger_name';
数据库拓展（extension）属性数据 √
登录/组角色（resource_group/role-p)
数据库语言（language）属性 √
view_rules
view_trigger
Table---- Constraint---下面五个 √
foreign_table
trigger_functions √
Function √
Materialized view √
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
