Re: Recording foreign key relationships for the system catalogs

On Mon, Feb 1, 2021, at 21:03, Tom Lane wrote:
>"Joel Jacobson" <joel(at)compiler(dot)org> writes:
>> The is_array OUT parameter doesn't say which of the possibly many fkcols that is the array column.
>
>Yeah, I didn't write the sgml docs yet, but the comments explain that
>the array is always the last fkcol. Maybe someday that won't be
>general enough, but we can cross that bridge when we come to it.

I've now fully migrated to using pg_get_catalog_foreign_keys()
instead of my own lookup tables, and have some additional hands-on experiences
to share with you.

I struggle to come up with a clean way to make use of is_array,
without being forced to introduce some CASE logic to figure out
if the fkcol is an array or not.

The alternative to join information_schema.columns and check data_type='ARRAY' is almost simpler,
but that seems wrong, since we now have is_array, and using it should be simpler than
joining information_schema.columns.

The best approach I've come up with so far is the CASE logic below:

WITH
foreign_keys AS
(
SELECT
fktable::text AS table_name,
unnest(fkcols) AS column_name,
pktable::text AS ref_table_name,
unnest(pkcols) AS ref_column_name,
--
-- is_array refers to the last fkcols column
--
unnest
(
CASE cardinality(fkcols)
WHEN 1 THEN ARRAY[is_array]
WHEN 2 THEN ARRAY[FALSE,is_array]
END
) AS is_array
FROM pg_get_catalog_foreign_keys()
)

If is_array would instead have been an boolean[], the query could have been written:

WITH
foreign_keys AS
(
SELECT
fktable::text AS table_name,
unnest(fkcols) AS column_name,
pktable::text AS ref_table_name,
unnest(pkcols) AS ref_column_name,
unnest(is_array) AS is_array
FROM pg_get_catalog_foreign_keys()
)

Maybe this can be written in a simpler way already.

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.

This would also be a more future proof solution,
and wouldn't require a code change to code using pg_get_catalog_foreign_keys(),
if we would ever add more complex cases in the future.

But even without increased future complexity,
I think the example above demonstrates a problem already today.

Maybe there is a simpler way to achieve what I'm trying to do,
i.e. to figure out if a specific fkcol is an array or not,
using some other simpler clever trick than the CASE variant above?

/Joel