We have a php script that executes \dt, \ds, \di, and \dv against our latest
database to capture the tables, sequences, etc. The script compares the
results of the commands to a file that defines the current schema to generate a
script that updates existing schemas to the latest.
I need to add functionality to this script for constraints.
Is there a simple way, such as a built in function, to find the constraints on
fields and the constraint type? I know that I can get some constraint info
from information_schema. I am just looking for an easy way rather than:
SELECT x.tblname::information_schema.sql_identifier AS table_name,
x.colname::information_schema.sql_identifier AS column_name,
x.cstrname::information_schema.sql_identifier AS constraint_name,
x.contype::information_schema.sql_identifier AS constraint_type
FROM (
( SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname,
nc.nspname, c.conname, c.contype
FROM pg_namespace nr, pg_class r, pg_attribute a,
pg_depend d, pg_namespace nc, pg_constraint c
WHERE nr.oid = r.relnamespace
AND r.oid = a.attrelid
AND d.refclassid = 'pg_class'::regclass::oid
AND d.refobjid = r.oid
AND d.refobjsubid = a.attnum
AND d.classid = 'pg_constraint'::regclass::oid
AND d.objid = c.oid
AND c.connamespace = nc.oid AND c.contype =
'c'::"char" AND r.relkind = 'r'::"char" AND NOT a.attisdropped
ORDER BY nr.nspname, r.relname, r.relowner, a.attname,
nc.nspname, c.conname
)
UNION ALL
SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname,
c.conname, c.contype
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
pg_constraint c
WHERE nr.oid = r.relnamespace
AND r.oid = a.attrelid
AND nc.oid = c.connamespace
AND CASE WHEN c.contype = 'f'::"char"
THEN r.oid = c.confrelid AND (a.attnum = ANY
(c.confkey))
ELSE r.oid = c.conrelid AND (a.attnum = ANY
(c.conkey))
END
AND NOT a.attisdropped
AND ( c.contype = 'p'::"char"
OR c.contype = 'u'::"char"
OR c.contype = 'f'::"char")
AND r.relkind = 'r'::"char"
)
x(tblschema, tblname, tblowner, colname, cstrschema, cstrname)
WHERE pg_has_role(x.tblowner, 'MEMBER'::text);
which yields:
table_name | column_name | constraint_name | constraint_type
---------------+----------------+------------------------+-----------------
map_shapes | map_shape_id | map_shapes_pkey | p
map_shapes | name | map_shapes_uname | u
map_shapes | description | map_shapes_udesc | u
map_shapes | use | map_shapes_uuse | u
map_shapes | map_shape_id | maps_fk_test_fkey | f
maps | mapid | maps_pkey | p
Thanks,
SteveE
