postgresql 实现replace into功能的代码

create table test(id int primary key , info text, crt_time timestamp );

with upsert as ( update test set info= 'test' ,crt_time=now() where id=1 returning *) insert into test select 1, 'test' ,now() where not exists ( select 1 from upsert where id=1);

INSERT INTO table_name VALUES () ON conflict (唯一索引字段) DO

UPDATE ...

create or replace FUNCTION test () RETURNS void AS

$body$

DECLARE

toalnum int ;

BEGIN

execute 'select sum(colname) into totalnum' ;

return ;

END ;

$body$

LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

create or replace FUNCTION test () RETURNS void AS

$body$

DECLARE

toalnum int ;

BEGIN

execute 'select sum(colname)' into totalnum;

return ;

END ;

$body$

LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;