By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table test1 (id1 serial, seq1 int, constraint pk1 primary key (id1, seq1));
create table test2 (id2 int, seq2 int, descr2 text, constraint pk2 primary key (id2, seq2), constraint fk1 foreign key (id2, seq2) references test1 (id1, seq1) ON DELETE RESTRICT ON UPDATE RESTRICT) ;
insert into test1 (seq1) values (1), (2), (3), (4), (5);
insert into test2 (id2, seq2, descr2) values (1,1,'A'), (2,2,'B'), (3,3,'C'), (4,4,'D'), (5,5,'E');
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 5
update test1 set seq1 = 11 where seq1 = 1 ;
ERROR: update or delete on table "test1" violates foreign key constraint "fk1" on table "test2" DETAIL: Key (id1, seq1)=(1, 1) is still referenced from table "test2".
SELECT conrelid::regclass AS table_name,
conname AS foreign_key,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f'
AND connamespace = 'public'::regnamespace
ORDER BY conrelid::regclass::text, contype DESC;
| table_name | foreign_key | pg_get_constraintdef |
|---|---|---|
| test2 | fk1 | FOREIGN KEY (id2, seq2) REFERENCES test1(id1, seq1) ON UPDATE RESTRICT ON DELETE RESTRICT |
SELECT 1
SELECT conrelid::regclass AS ReferencingTable
, a.ReferencingKey
, b.ReferencedTable
, b.ReferencedKey
FROM pg_constraint AS pg
CROSS JOIN LATERAL
( SELECT '(' || string_agg(conrelid::regclass || '.' || fkey, ',' ORDER BY fkey.ORDINALITY) || ')' AS ReferencingKey
FROM pg_get_constraintdef(pg.oid) AS fk
CROSS JOIN LATERAL string_to_table(translate(regexp_substr(fk, '\([^\)]*\)', 1, 1), '() ', ''), ',') WITH ORDINALITY AS fkey
) AS a
CROSS JOIN LATERAL
( SELECT '(' || string_agg(ReferencedTable || '.' || rkey, ',' ORDER BY rkey.ORDINALITY) || ')' AS ReferencedKey
, ReferencedTable
FROM pg_get_constraintdef(pg.oid) AS fk
CROSS JOIN LATERAL split_part((regexp_match(fk, 'REFERENCES\s\w+'))[1], ' ', 2) AS ReferencedTable
CROSS JOIN LATERAL string_to_table(translate(regexp_substr(fk, '\([^\)]*\)', 1, 2), '() ', ''), ',') WITH ORDINALITY AS rkey
GROUP BY ReferencedTable
) AS b
WHERE contype = 'f'
AND connamespace = 'public' ::regnamespace
| referencingtable | referencingkey | referencedtable | referencedkey |
|---|---|---|---|
| test2 | (test2.id2,test2.seq2) | test1 | (test1.id1,test1.seq1) |
SELECT 1
CREATE OR REPLACE FUNCTION test(IN schema text, OUT Referencing_Table text, OUT Referencing_Key text, OUT Referenced_Table text)
RETURNS setof record LANGUAGE plpgsql AS
$$
DECLARE
_row record ;
BEGIN
FOR _row IN
( SELECT conrelid::regclass AS ReferencingTable
, a.ReferencingKey
, b.ReferencedTable
, b.ReferencedKey
FROM pg_constraint AS pg
CROSS JOIN LATERAL
( SELECT '(' || string_agg(conrelid::regclass || '.' || fkey, ',' ORDER BY fkey.ORDINALITY) || ')' AS ReferencingKey
FROM pg_get_constraintdef(pg.oid) AS fk
CROSS JOIN LATERAL string_to_table(translate(regexp_substr(fk, '\([^\)]*\)', 1, 1), '() ', ''), ',') WITH ORDINALITY AS fkey
) AS a
CROSS JOIN LATERAL
( SELECT '(' || string_agg(ReferencedTable || '.' || rkey, ',' ORDER BY rkey.ORDINALITY) || ')' AS ReferencedKey
, ReferencedTable
FROM pg_get_constraintdef(pg.oid) AS fk
CROSS JOIN LATERAL split_part((regexp_match(fk, 'REFERENCES\s\w+'))[1], ' ', 2) AS ReferencedTable
CROSS JOIN LATERAL string_to_table(translate(regexp_substr(fk, '\([^\)]*\)', 1, 2), '() ', ''), ',') WITH ORDINALITY AS rkey
GROUP BY ReferencedTable
) AS b
WHERE contype = 'f'
AND connamespace = schema ::regnamespace )
LOOP
RETURN QUERY EXECUTE FORMAT (
'SELECT %L :: text AS Referencing_Table, %s :: text AS Referencing_Key, %L :: text AS Referenced_Table
FROM %I LEFT JOIN %I ON %s = %s
WHERE %s IS NULL'
, _row.ReferencingTable
, _row.ReferencingKey
, _row.ReferencedTable
CREATE FUNCTION
SELECT * FROM test('public');
| referencing_table | referencing_key | referenced_table |
|---|
SELECT 0