Question

postgresql存储过程怎么写

Answer 1

写个触发器 插入之前执行触发器

-- 创建一个测试表

create table test(id int primary key , name varchar(50));

-- 触发器 插入前ID如果已经存在则替换name的值
CREATE OR REPLACE function _replace() RETURNS TRIGGER AS $INSERT$

declare

_has int ;

BEGIN

select id from test where id = NEW.id into _has;

raise notice 'ddd:%' , _has;

if _has > 0 then
update test set name = NEW.name where id = NEW.id;

RETURN null;

end if;

return NEW;

END;

$INSERT$

LANGUAGE PLPGSQL;

-- 给表加上触发器
CREATE TRIGGER tbefore BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE _replace();

-- 插入两个值
insert into test(id , name) values(1,'1');
insert into test(id , name) values(1,'6');

select * from test;

结果：

pumpkin=> select * from test;
id | name
----+------
1 | 6
(1 行记录)

时间：1.474 ms