Re: Unexpected behavior of DROP VIEW/TABLE IF EXISTS

2018-06-26 17:23 GMT+02:00 Peter Moser <pitiz29a(at)gmail(dot)com>:

> Hi,
> I want to delete a table X, that may not exist, hence I execute
>
> DROP TABLE IF EXISTS X;
>
> However, if X is a view, I get an error
>
> ERROR: "X" is not a table
>
HINT: Use DROP VIEW to remove a view.
> SQL state: 42809
>
> That is unexpected and also difficult to handle, if I want to be sure that
> I can delete all old tables *and* views called X, and create a new
> view for instance with
>
> CREATE VIEW X AS ....
>
> I cannot do that safely, because having both DROP commands would for sure
> cause an error and therefore a rollback.
>
> What do you think, is it worth to create a patch to solve this issue,
> where a DROP TABLE does not fail, if the given name is actually a VIEW or
> vice-versa?
>

DROP TABLE should to remove table and nothing else, like DROP VIEW should
to drop just view and nothing else. It is safeguard.

what is hard on code

do $$ declare r record;
begin
for r in select table_name, case table_type when 'BASE TABLE' then
'table' when 'VIEW' then 'view' end as tp
from information_schema.tables
where table_type in ('BASE TABLE', 'VIEW')
and table_name = 'foo'
loop
raise notice '%', format('drop %s %I', r.tp, r.table_name);
end loop;
end $$;

replace raise notice by execute if you really want to drop some objects.

Regards

Pavel

>
> Best regards,
> Peter
>
>