Re: Query about foreign key details for php framework

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
>> On Sat, 27 Feb 2016 at 07:35 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(at)gmail(dot)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.
>
> http://www.postgresql.org/docs/9.5/interactive/catalogs.html
>
> David J.
> ​
>