Re: Recording foreign key relationships for the system catalogs - Mailing list pgsql-hackers
| From | Joel Jacobson |
|---|---|
| Subject | Re: Recording foreign key relationships for the system catalogs |
| Date | |
| Msg-id | 9542ec0d-2ade-444d-95e4-92b335ae32f2@www.fastmail.com Whole thread Raw |
| In response to | Re: Recording foreign key relationships for the system catalogs ("Joel Jacobson" <joel@compiler.org>) |
| List | pgsql-hackers |
On Wed, Feb 3, 2021, at 21:41, Joel Jacobson wrote:
>Otherwise I think it would be more natural to change both is_array and is_opt
>to boolean[] with the same cardinality as fkcols and pkcols,
>to allow unnest()ing of them as well.
Another option would perhaps be to add a new
system view in src/backend/catalog/system_views.sql
I see there are other cases with a slightly more complex view
using a function with a similar name, such as
the pg_stat_activity using pg_stat_get_activity().
Similar to this, maybe we could add a pg_catalog_foreign_keys view
using the output from pg_get_catalog_foreign_keys():
Example usage:
SELECT * FROM pg_catalog_foreign_keys
WHERE fktable = 'pg_constraint'::regclass
AND pktable = 'pg_attribute'::regclass;
fkid | fktable | fkcol | pktable | pkcol | is_array | is_opt | ordinal_position
------+---------------+-----------+--------------+----------+----------+--------+------------------
48 | pg_constraint | conkey | pg_attribute | attnum | t | t | 1
48 | pg_constraint | conrelid | pg_attribute | attrelid | f | f | 2
49 | pg_constraint | confkey | pg_attribute | attnum | t | f | 1
49 | pg_constraint | confrelid | pg_attribute | attrelid | f | f | 2
(4 rows)
The point of this would be to avoid unnecessary increase of data model complexity,
which I agree is not needed, since we only need single booleans as of today,
but to provide a more information_schema-like system view,
i.e. with columns on separate rows, with ordinal_position.
Since we don't have any "constraint_name" for these,
we need to enumerate the fks first, to let ordinal_position
be the position within each such fkid.
Here is my proposal on how to implement:
CREATE VIEW pg_catalog_foreign_keys AS
WITH
enumerate_fks AS (
SELECT
*,
ROW_NUMBER() OVER () AS fkid
FROM pg_catalog.pg_get_catalog_foreign_keys()
),
unnest_cols AS (
SELECT
C.fkid,
C.fktable,
unnest(C.fkcols) AS fkcol,
C.pktable,
unnest(C.pkcols) AS pkcol,
unnest(
CASE cardinality(fkcols)
WHEN 1 THEN ARRAY[C.is_array]
WHEN 2 THEN ARRAY[FALSE,C.is_array]
END
) AS is_array,
unnest(
CASE cardinality(fkcols)
WHEN 1 THEN ARRAY[C.is_opt]
WHEN 2 THEN ARRAY[FALSE,C.is_opt]
END
) AS is_opt
FROM enumerate_fks AS C
)
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY U.fkid
ORDER BY U.fkcol, U.pkcol
) AS ordinal_position
FROM unnest_cols AS U;
I think both the pg_get_catalog_foreign_keys() function
and this view are useful in different ways,
so it's good to provide both.
Only providing pg_get_catalog_foreign_keys() will
arguably mean some users of the function will need to implement
something like the same as above on their own, if they need the is_array and is_opt
value for a specific fkcol.
/Joel
pgsql-hackers by date: