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

From Stuart
Subject Re: Query about foreign key details for php framework
Date
Msg-id CALmuyMr82esykuWcY16hwZ2WgfWodV+3AnZBV6=tBKr8ve0EDg@mail.gmail.com
Whole thread Raw
In response to Re: Query about foreign key details for php framework  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-sql
David,

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";



On Sat, Feb 27, 2016 at 11:56 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Sat, 27 Feb 2016 at 07:35 Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/26/2016 10:47 AM, David Binney wrote:
> That is exactly the desired result, but in my db it is returning 2k rows
> with exactly the same query, even filtered to a specific table.

Note to self, read the entire doc page:

http://www.postgresql.org/docs/9.5/interactive/information-schema.html
"
Note: When querying the database for constraint information, it is
possible for a standard-compliant query that expects to return one row
to return several. This is because the SQL standard requires constraint
names to be unique within a schema, but PostgreSQL does not enforce this
restriction. PostgreSQL automatically-generated constraint names avoid
duplicates in the same schema, but users can specify such duplicate names.

     This problem can appear when querying information schema views such
as check_constraint_routine_usage, check_constraints,
domain_constraints, and referential_constraints. Some other views have
similar issues but contain the table name to help distinguish duplicate
rows, e.g., constraint_column_usage, constraint_table_usage,
table_constraints.

On Fri, Feb 26, 2016 at 9:29 PM, David Binney <donkeysoft@gmail.com> wrote:
Hey adrian, 

You are correct that the distinct will chomp the resultset down to the correct count, I am just concerned that there will be cases where it might not be accurate between the "rc" and the "kcu" joins as there is no table reference. I have simplified the query right down to just the join that i am unsure about. You can see below that as soon as i add the  rc.unique_constraint_name, the distinct is no longer returning one row. In this case its fine because the rc values are the same and would distinct away, but there might be a case where they are diferent and you would have two rows and not know which values are correct? 

​David,

You are correct.  Since referential_constraints lacks a table name you are hosed for this particular query - unless you ensure that your database is also standard compliant by not introducing duplicate constraint names within the same schema.  If you cannot do that then the only solution to obtain a correct result is to use pg_catalog tables directly and bypass information_schema altogether.


David J.

pgsql-sql by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Query about foreign key details for php framework
Next
From: David Binney
Date:
Subject: Re: Query about foreign key details for php framework