Skip to content

Instantly share code, notes, and snippets.

@juike
Created Mar 25, 2014
Embed
What would you like to do?
Postgresql table inherits view
WITH RECURSIVE tables_tree (table_oid, path) AS (
SELECT I.inhparent AS table_oid, '{}'::oid[] AS path
FROM pg_inherits I
LEFT JOIN pg_inherits I2 ON I.inhparent = I2.inhrelid
WHERE I2.inhparent IS NULL
UNION
SELECT I.inhrelid, TT.path || I.inhparent
FROM pg_inherits I
JOIN tables_tree TT ON TT.table_oid = I.inhparent
)
SELECT
NC.nspname || '.' || C.relname AS table_name,
NP.nspname || '.' || P.relname AS parent_table_name
FROM tables_tree TT
JOIN pg_class C ON TT.table_oid = C.oid
LEFT JOIN pg_class P ON TT.path[1] = P.oid
JOIN pg_namespace NC ON C.relnamespace = NC.oid
LEFT JOIN pg_namespace NP ON P.relnamespace = NP.oid;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment