How can I find a table's views in PostgreSQL?-CodePudding

I need to find all view based on table my_table. I try query

select *
from information_schema.views
where view_definition ilike '%my_table%'

and get empty table. That means I have no view based on my_table. But after trying to delete my_table I get an error "cannot drop table my_table because other objects depend on it". I look more carefully with

select *
from information_schema.views
where table_name = 'my_view'

and find row with view based on my table with NULL in view_definition.

Why definition can be NULL? Is there any another way to find view depends on table?

CodePudding user response：

Read the "detail" message that comes with the error message. It will tell you what objects cause the problem. Those are the dependent objects that prevent the table from being dropped.

To drop all these dependencies, you can use DROP TABLE ... CASCADE, like the hint to the error message says.

CodePudding user response：

Thats what I needed. As I say, I have a list of tables and there are more than 30 elements. It is not directly answer on my question, but it solve the problem

do $$
declare names text[] := array['my_table', 'my_table1', 'my_table2'];

declare i int4;
begin
    for i in 1..3 loop
        begin
        
        execute 'drop table if exists my_schema.' || names[i];
        
        EXCEPTION WHEN OTHERS then
            -- nothing
        end;
    end loop;

end;
$$;