Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create sequence foo_seq minvalue 1 maxvalue 5 cycle;
create table foo (
    id   int  default nextval('foo_seq')  primary key,
    data text
);
create or replace procedure log_foo(p_data text) 
  language sql 
as $$
   insert into foo (data) values (p_data) 
       on conflict (id)
       do update set data=excluded.data; 
$$;

do $$
begin 
   call log_foo('1'); 
   call log_foo('2'); 
   call log_foo('3');
   call log_foo('4');
end ;
$$;

select * from foo order by id;


id
data


1
1

2
2

3
3

4
4


create or replace function foo_log(p_data text) 
  returns integer
  language sql 
as $$
   insert into foo (data) values (p_data) 
       on conflict (id)
       do update set data=excluded.data 
       returning id;
$$;
 
do $$
begin
  perform foo_log('1a'); 
  perform foo_log('2a');
  perform foo_log('3a');
  perform foo_log('4a');
end; 
$$;

select * from foo order by id;


id
data


1
2a

2
3a

3
4a

4
4

5
1a


select foo_log('5a');

select * from foo order by id; 


foo_log


4




id
data


1
2a

2
3a

3
4a

4
5a

5
1a