Re: Unexpected behavior of DROP VIEW/TABLE IF EXISTS
| От | Pavel Stehule |
|---|---|
| Тема | Re: Unexpected behavior of DROP VIEW/TABLE IF EXISTS |
| Дата | |
| Msg-id | CAFj8pRB9o8ixHFy96fM71Lk+gK-4sprjH3q8s9fM8K_SFxFrng@mail.gmail.com обсуждение исходный текст |
| Ответ на | Unexpected behavior of DROP VIEW/TABLE IF EXISTS (Peter Moser <pitiz29a@gmail.com>) |
| Ответы |
Re: Unexpected behavior of DROP VIEW/TABLE IF EXISTS
Unexpected behavior of DROP VIEW/TABLE IF EXISTS |
| Список | pgsql-hackers |
2018-06-26 17:23 GMT+02:00 Peter Moser <pitiz29a@gmail.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
В списке pgsql-hackers по дате отправления: