PostgreSQL/greenplum query depends on the table View

In PostgreSQL and greenplum, we often need to recreate a table. To facilitate rollback, we usually rename the original table and then recreate the original table.

In this way, there will be a problem: the View dependent on the original table or the rename table is not dependent on the new table. This is because the view is defined based on the table oid. After the original table rename, the oId does not change, which leads to a table with incorrect view dependency.

It is troublesome to query a view that depends on a table. You can use the pg_depend data dictionary to query the view, but it is inconvenient to use.

The following provides a view to facilitate query of views dependent on tables.

1. Define a function to quickly convert the oId to schemaname. tablename.

 
Create or replace function public. tabname_oid (a oid) returns textas $ return plpy.exe cute ("select B. nspname | '. '|. relname as tabname from pg_class A, pg_namespace B wherea. relnamespace = B. oid and. oid = % s "% (A) [0] ['tabname']; $ language plpythonu;
 

2. Create a query view

Create view public. views_on_tables as select tabname_oid (C. ev_class) viewname, tabname_oid (PC. oid) tablename from pg_depend A, pg_depend B, pg_class PC, pg_rewrite c Where. refclassid = 1259 and B. deptype = 'I' and. classid = 2618 and. objid = B. objid and. classid = B. classid and. refclassid = B. refclassid and. refobjid <> B. refobjid and PC. oid =. refobjid and C. oid = B. objid group by C. ev_class, PC. oid;
 

3. The effect is as follows: