Search Postgresql Archives

simple way to find the constraints

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux