仅在列存在时才执行更新

Is it possible to execute an update conditionally if a column exists? For instance, I may have a column in a table and if that column exists I want that update executed, otherwise, just skip it (or catch its exception).

如果列存在,是否可以有条件地执行更新?例如,我可能在表中有一列,如果该列存在,我希望执行更新,否则,只需跳过它(或捕获它的异常)。

2 个解决方案

#1

You can do it inside a function. If you don't want to use the function later you can just drop it afterwards.

你可以在一个函数内完成它。如果您以后不想使用该功能,可以稍后再删除它。

To know if a column exists in a certain table, you can try to fetch it using a select(or a perform, if you're gonna discard the result) in information_schema.columns.

要知道某个列是否存在于某个表中,您可以尝试使用select_schema.columns中的select(或者执行,如果要丢弃结果)来获取它。

The query bellow creates a function that searches for a column bar in a table foo, and if it finds it, updates its value. Later the function is run, then droped.

下面的查询创建一个函数,用于搜索表foo中的列栏,如果找到它,则更新其值。稍后该功能运行,然后下降。

create function conditional_update() returns void as
$$
begin
  perform column_name from information_schema.columns where table_name= 'foo' and column_name = 'bar';
  if found then
    update foo set bar = 12345;
  end if;
end;
$$ language plpgsql;
select conditional_update();
drop function conditional_update();

#2

With the following table as example :

以下表为例:

CREATE TABLE mytable (
    idx INT
    ,idy INT
    );

insert into mytable values (1,2),(3,4),(5,6);

you can create a custom function like below to update:

您可以创建如下所示的自定义函数来更新:

create or replace function fn_upd_if_col_exists(_col text,_tbl text,_val int) returns void as 
$$
begin
If exists (select 1 
from information_schema.columns  
where table_schema='public' and table_name=''||_tbl||'' and column_name=''||_col||'' ) then
execute format('update mytable set '||_col||'='||_val||'');
raise notice 'updated';
else
raise notice 'column %s doesn''t exists on table %s',_col,_tbl;
end if;
end;
$$
language plpgsql

and you can call this function like:

你可以像这样调用这个函数:

select fn_upd_if_col_exists1('idz','mytable',111) -- won't update raise "NOTICE:  column idz deosnt exists on table mytables"

select fn_upd_if_col_exists1('idx','mytable',111) --will upadate column idx with value 1111 "NOTICE:  updated"

#1