Question 89931 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;
$$;

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search