Skip to main content
added 11 characters in body
Source Link

I think something like this should do it:

SELECT DISTINCT
  pg_class.oid::regclass
FROM pg_rewrite
JOIN pg_depend ON
  pg_depend.classid = 'pg_rewrite'::regclass AND
  pg_depend.objid = pg_rewrite.oid AND
  pg_depend.refclassid = 'pg_class'::regclass AND
  pg_depend.refobjid <> pg_rewrite.ev_class
JOIN pg_class ON
  pg_class.oid = pg_depend.refobjid AND
  pg_class.relkind IN ('r','f','p','v','m')
WHERE
  pg_rewrite.ev_class = 'rental_by_category'::regclass

This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a RECURSIVE CTE).

The pg_class.relkind IN ('r','f','p','v','m') constraint listscovers all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docsdocs.

Many different kinds of dependencies are stored in pg_depend, and not all of them are relevant to your question. For example, the view's pg_rewrite entry has a dependency on its own pg_class entry; the pg_depend.refobjid <> pg_rewrite.ev_class constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.

I think something like this should do it:

SELECT DISTINCT
  pg_class.oid::regclass
FROM pg_rewrite
JOIN pg_depend ON
  pg_depend.classid = 'pg_rewrite'::regclass AND
  pg_depend.objid = pg_rewrite.oid AND
  pg_depend.refclassid = 'pg_class'::regclass AND
  pg_depend.refobjid <> pg_rewrite.ev_class
JOIN pg_class ON
  pg_class.oid = pg_depend.refobjid AND
  pg_class.relkind IN ('r','f','p','v','m')
WHERE
  pg_rewrite.ev_class = 'rental_by_category'::regclass

This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a RECURSIVE CTE).

The pg_class.relkind constraint lists all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docs.

Many different kinds of dependencies are stored in pg_depend, and not all of them are relevant to your question. For example, the view's pg_rewrite entry has a dependency on its own pg_class entry; the pg_depend.refobjid <> pg_rewrite.ev_class constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.

I think something like this should do it:

SELECT DISTINCT
  pg_class.oid::regclass
FROM pg_rewrite
JOIN pg_depend ON
  pg_depend.classid = 'pg_rewrite'::regclass AND
  pg_depend.objid = pg_rewrite.oid AND
  pg_depend.refclassid = 'pg_class'::regclass AND
  pg_depend.refobjid <> pg_rewrite.ev_class
JOIN pg_class ON
  pg_class.oid = pg_depend.refobjid AND
  pg_class.relkind IN ('r','f','p','v','m')
WHERE
  pg_rewrite.ev_class = 'rental_by_category'::regclass

This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a RECURSIVE CTE).

pg_class.relkind IN ('r','f','p','v','m') covers all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docs.

Many different kinds of dependencies are stored in pg_depend, and not all of them are relevant to your question. For example, the view's pg_rewrite entry has a dependency on its own pg_class entry; the pg_depend.refobjid <> pg_rewrite.ev_class constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.

Source Link

I think something like this should do it:

SELECT DISTINCT
  pg_class.oid::regclass
FROM pg_rewrite
JOIN pg_depend ON
  pg_depend.classid = 'pg_rewrite'::regclass AND
  pg_depend.objid = pg_rewrite.oid AND
  pg_depend.refclassid = 'pg_class'::regclass AND
  pg_depend.refobjid <> pg_rewrite.ev_class
JOIN pg_class ON
  pg_class.oid = pg_depend.refobjid AND
  pg_class.relkind IN ('r','f','p','v','m')
WHERE
  pg_rewrite.ev_class = 'rental_by_category'::regclass

This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a RECURSIVE CTE).

The pg_class.relkind constraint lists all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docs.

Many different kinds of dependencies are stored in pg_depend, and not all of them are relevant to your question. For example, the view's pg_rewrite entry has a dependency on its own pg_class entry; the pg_depend.refobjid <> pg_rewrite.ev_class constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.