simple way to find the constraints

["Ertel, Steve" <Steve.Ertel@xxxxxxxxxxxxx>]



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 = ''::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