如何根据postgres中的IF条件删除表格？

I'm trying to drop a table on startup based on a condition:

我试图根据条件在启动时删除一个表:

IF NOT EXISTS (select * from pg_class where relname = 'mytable' and relpersistence = 'u') 
DROP TABLE IF EXISTS mytable

Result: Syntaxerror at 'IF', SQL state: 42601. Why? How can I drop a table based on a condition, if I'm not allowed to use IF?

结果:'IF'处的语法错误,SQL状态:42601。为什么?如果我不允许使用IF,我如何根据条件删除表格?

2 个解决方案

#1

IF can't be used in SQL, this is only valid for PL/pgSQL.

IF不能在SQL中使用,这仅对PL / pgSQL有效。

You need to do this with dynamic SQL inside an anonymous PL/pgSQL block. Something like:

您需要在匿名PL / pgSQL块中使用动态SQL执行此操作。就像是:

do
$$
declare
  l_count integer;
begin
  select count(*)
     into l_count
  from pg_class c
    join pg_namespace nsp on c.relnamespace = nsp.oid
  where c.relname = 'mytable' 
    and c.relpersistence = 'u'
    and nsp.nspname = 'public';

  if l_count = 1 then 
    execute 'drop table mytable';
  end if;

end;
$$

You probably should extend the select statement to join against pg_namespace and include the schema name in your where condition to make sure you are not accidently dropping a table from the wrong schema.

您可能应该将select语句扩展为针对pg_namespace加入,并在where条件中包含模式名称,以确保您不会意外地从错误的模式中删除表。

#2

Already accepted answer by a_horse_with_no_name will works though You can create a Custom Function if you want to use the same task for other tables in future, so you should create a function like below:

a_horse_with_no_name已经接受的答案将有效但如果您希望将来对其他表使用相同的任务,则可以创建自定义函数,因此您应该创建如下函数:

create or replace function drop_table (_tbl varchar) returns void as
$$
begin
if exists(select 1
          from pg_class c
          join pg_namespace nsp on c.relnamespace = nsp.oid
          where c.relname = ''||_tbl||'' 
            and c.relpersistence = 'u'
            and nsp.nspname = 'public') then
  execute format('DROP TABLE %s',_tbl);
  raise notice 'Table %s Deleted',_tbl;
else
  raise notice 'Table %s Not Deleted',_tbl;
end if;
end;
$$
language plpgsql

and simply call this function whenever you want

并随时调用此功能

select drop_table('mytable');

select drop_table('mytable_1')

#1