Postgresql code to implement replace into function

PostgreSQL 9.5-

Use a function or with

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);

PostgreSQL 9.5+

PostgreSQL 9.5 introduces a new feature, UPSERT (insert on conflict do), which returns directly when an insert encounters a constraint error, or executes UPDATE instead.

INSERT INTO table_name VALUES() ON conflict (Unique index field) DO
UPDATE ...

Supplement: Summary of the usage of select into in PostgreSQL

In ordinary SQL, postgresql supports select...into...

However, select is not supported when calling dynamically...... into...

for example:

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;

The above situation will be reported. . . . .

Because it should be modified as follows

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;

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.