PostgreSQL – How to List Tables Used by A View

ByAdmin

To list the tables used by a view in PostgreSQL, you can query the system catalog to inspect the view’s underlying SQL query. PostgreSQL stores view definitions in the pg_views system catalog table. Here’s how you can retrieve the tables used by a specific view:

SELECT table_name 
 FROM information_schema.tables 
 WHERE table_schema = 'public' 
  AND table_name IN ( SELECT regexp_replace(regexp_replace(view_definition, E'^.*\\bFROM\\s+', ''), '\\s.*$', '', 'g') AS table_name 
 FROM pg_views 
 WHERE viewname = 'your_view_name' );

Replace 'public' with the schema where your view is located. Replace 'your_view_name' with the name of the view you want to examine.

This query retrieves the names of tables used by the specified view by parsing the view definition and extracting table names from the FROM clause of the SQL query that defines the view. It then checks if those tables exist in the specified schema.

Similar Posts

PostgreSQL – How to Get DDL of a Materialized View

ByAdmin

To retrieve the Data Definition Language (DDL) for a materialized view in PostgreSQL, you can use the pg_get_viewdef function, which is a built-in function for getting the definition (DDL) of various database objects, including materialized views. Here’s the syntax to retrieve the DDL for a materialized view: SELECT pg_get_viewdef(‘your_materialized_view_name’::regclass, true); where your_materialized_view_name is the name…

PostgreSQL – How to Change Role within a Database Session

ByAdmin

You can change the current role within a database session in PostgreSQL database using SET ROLE statement . Changing the role allows you to temporarily assume the permissions and privileges associated with a different role, which can be useful for specific tasks or actions that require different access rights. This can be particularly helpful in…

PostgreSQL – How to Generate a Random Number within a Range

ByAdmin

You can use the RANDOM() function to generate a random floating-point number between 0 and 1 in PostgreSQL database. Here’s the basic syntax for using the RANDOM() function: SELECT RANDOM(); This will return a random number in the range [0, 1), meaning it can be any decimal value between 0 (inclusive) and 1 (exclusive). If…