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 audit_trail AS
SELECT * FROM (
VALUES
('harold_gim@yahoo.com', 'hgimenez@hotmail.com')
, ('hgimenez@hotmail.com', 'harold.gimenez@gmail.com')
, ('harold.gimenez@gmail.com', 'harold@heroku.com')
, ('foo@bar.com', 'bar@baz.com')
, ('bar@baz.com', 'barbaz@gmail.com')
) t(old_email, new_email);
CREATE TABLE iter1 AS
SELECT old_email, new_email
FROM audit_trail
WHERE old_email = 'harold_gim@yahoo.com';
CREATE TABLE iter2 AS
SELECT a.old_email, a.new_email
FROM audit_trail a
JOIN iter1 b ON (a.old_email = b.new_email);
SELECT 5
SELECT 1
SELECT 1
TABLE audit_trail;
| old_email | new_email |
|---|---|
| harold_gim@yahoo.com | hgimenez@hotmail.com |
| hgimenez@hotmail.com | harold.gimenez@gmail.com |
| harold.gimenez@gmail.com | harold@heroku.com |
| foo@bar.com | bar@baz.com |
| bar@baz.com | barbaz@gmail.com |
SELECT 5
TABLE iter1;
| old_email | new_email |
|---|---|
| harold_gim@yahoo.com | hgimenez@hotmail.com |
SELECT 1
TABLE iter2;
| old_email | new_email |
|---|---|
| hgimenez@hotmail.com | harold.gimenez@gmail.com |
SELECT 1
WITH RECURSIVE all_emails AS (
SELECT *
FROM audit_trail
WHERE old_email = 'harold_gim@yahoo.com'
UNION ALL -- union all!
SELECT t.*
FROM all_emails a
JOIN audit_trail t ON t.old_email = a.new_email
)
TABLE all_emails;
| old_email | new_email |
|---|---|
| harold_gim@yahoo.com | hgimenez@hotmail.com |
| hgimenez@hotmail.com | harold.gimenez@gmail.com |
| harold.gimenez@gmail.com | harold@heroku.com |
SELECT 3
SELECT * FROM iter1
UNION
SELECT * FROM iter2;
| old_email | new_email |
|---|---|
| harold_gim@yahoo.com | hgimenez@hotmail.com |
| hgimenez@hotmail.com | harold.gimenez@gmail.com |
SELECT 2
SELECT * FROM iter1
UNION ALL -- union all!
SELECT * FROM iter2;
| old_email | new_email |
|---|---|
| harold_gim@yahoo.com | hgimenez@hotmail.com |
| hgimenez@hotmail.com | harold.gimenez@gmail.com |
SELECT 2
-- to be sure:
WITH RECURSIVE all_emails AS (
SELECT *, 1 AS lvl
FROM audit_trail
WHERE old_email = 'harold_gim@yahoo.com'
UNION ALL -- union all!
SELECT t.*, a.lvl + 1
FROM all_emails a
JOIN audit_trail t ON t.old_email = a.new_email
)
TABLE all_emails
ORDER BY lvl;
| old_email | new_email | lvl |
|---|---|---|
| harold_gim@yahoo.com | hgimenez@hotmail.com | 1 |
| hgimenez@hotmail.com | harold.gimenez@gmail.com | 2 |
| harold.gimenez@gmail.com | harold@heroku.com | 3 |
SELECT 3
-- for multiple trails at once:
WITH RECURSIVE all_emails AS (
SELECT *, ctid AS trail_id, 1 AS lvl
FROM audit_trail
WHERE old_email IN ('harold_gim@yahoo.com', 'foo@bar.com')
UNION ALL -- union all!
SELECT t.*, a.trail_id, a.lvl + 1
FROM all_emails a
JOIN audit_trail t ON t.old_email = a.new_email
)
TABLE all_emails
ORDER BY trail_id, lvl;
| old_email | new_email | trail_id | lvl |
|---|---|---|---|
| harold_gim@yahoo.com | hgimenez@hotmail.com | (0,1) | 1 |
| hgimenez@hotmail.com | harold.gimenez@gmail.com | (0,1) | 2 |
| harold.gimenez@gmail.com | harold@heroku.com | (0,1) | 3 |
| foo@bar.com | bar@baz.com | (0,4) | 1 |
| bar@baz.com | barbaz@gmail.com | (0,4) | 2 |
SELECT 5