Re: Query about foreign key details for php framework - Mailing list pgsql-sql

Glad you were able to get that sorted.  Just as FYI, you can use the system tables to query on to get similar info.  The following query allows foreign key to contain up to 3 fields;

# \d+ admin.foreignkey_constraints_view
          View "admin.foreignkey_constraints_view"
     Column      | Type | Modifiers | Storage  | Description
-----------------+------+-----------+----------+-------------
 oid             | oid  |           | plain    |
 table_name      | name |           | plain    |
 constraint_name | name |           | plain    |
 reference_key   | name |           | plain    |
 on_update       | text |           | extended |
 on_delete       | text |           | extended |
 foreign_table   | name |           | plain    |
 foreign_key     | name |           | plain    |
View definition:
 SELECT DISTINCT c.oid,
    t.relname AS table_name,
    c.conname AS constraint_name,
        CASE
            WHEN cardinality(c.conkey) = 1 THEN k.attname
            WHEN cardinality(c.conkey) = 2 THEN ((k.attname::text || ', '::text) || k2.attname::text)::name
            WHEN cardinality(c.conkey) = 3 THEN ((((k.attname::text || ', '::text) || k2.attname::text) || ', '::text) || k3.attname::text)::name
            ELSE NULL::name
        END AS reference_key,
        CASE
            WHEN c.confupdtype = 'a'::"char" THEN 'NO ACTION'::text
            WHEN c.confupdtype = 'c'::"char" THEN 'CASCADE'::text
            WHEN c.confupdtype = 'd'::"char" THEN 'SET DEFAULT'::text
            WHEN c.confupdtype = 'n'::"char" THEN 'SET NULL'::text
            WHEN c.confupdtype = 'r'::"char" THEN 'RESTRICT'::text
            ELSE NULL::text
        END AS on_update,
        CASE
            WHEN c.confdeltype = 'a'::"char" THEN 'NO ACTION'::text
            WHEN c.confdeltype = 'c'::"char" THEN 'CASCADE'::text
            WHEN c.confdeltype = 'd'::"char" THEN 'SET DEFAULT'::text
            WHEN c.confdeltype = 'n'::"char" THEN 'SET NULL'::text
            WHEN c.confdeltype = 'r'::"char" THEN 'RESTRICT'::text
            ELSE NULL::text
        END AS on_delete,
    ft.relname AS foreign_table,
        CASE
            WHEN cardinality(c.confkey) = 1 THEN fk.attname
            WHEN cardinality(c.confkey) = 2 THEN ((fk.attname::text || ', '::text) || fk2.attname::text)::name
            WHEN cardinality(c.confkey) = 3 THEN ((((fk.attname::text || ', '::text) || fk2.attname::text) || ', '::text) || fk3.attname::text)::name
            ELSE NULL::name
        END AS foreign_key
   FROM pg_constraint c
     JOIN pg_class t ON c.conrelid = t.oid
     JOIN pg_class ft ON c.confrelid = ft.oid
     JOIN pg_attribute k ON t.oid = k.attrelid AND c.conkey[1] = k.attnum
     LEFT JOIN pg_attribute k2 ON t.oid = k2.attrelid AND c.conkey[2] = k2.attnum
     LEFT JOIN pg_attribute k3 ON t.oid = k3.attrelid AND c.conkey[3] = k3.attnum
     JOIN pg_attribute fk ON ft.oid = fk.attrelid AND c.confkey[1] = fk.attnum
     LEFT JOIN pg_attribute fk2 ON ft.oid = fk2.attrelid AND c.confkey[2] = fk2.attnum
     LEFT JOIN pg_attribute fk3 ON ft.oid = fk3.attrelid AND c.confkey[3] = fk3.attnum
  WHERE c.contype = 'f'::"char";