From: | "Ertel, Steve" <Steve(dot)Ertel(at)infimatic(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | simple way to find the constraints |
Date: | 2007-12-12 21:29:56 |
Message-ID: | 863ECFDF04C8804ABDF3559D87F157C638E5DF@MAGPTCPEXC02.na.mag-ias.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 (
( 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
From | Date | Subject | |
---|---|---|---|
Next Message | Rodrigo De León | 2007-12-12 21:40:02 | Re: Extract last 4 characters from string? |
Previous Message | D. Dante Lorenso | 2007-12-12 21:11:50 | Extract last 4 characters from string? |