simple way to find the constraints
| От | Ertel, Steve |
|---|---|
| Тема | simple way to find the constraints |
| Дата | |
| Msg-id | 863ECFDF04C8804ABDF3559D87F157C638E5DF@MAGPTCPEXC02.na.mag-ias.net обсуждение исходный текст |
| Список | pgsql-general |
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 (
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
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
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
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
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);
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
---------------+----------------+------------------------+-----------------
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
В списке pgsql-general по дате отправления: