simple way to find the constraints

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

Browse pgsql-general by date

  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?