how to return data from insert into ... on conflict ... returning ... into - Mailing list pgsql-general
| From | Les |
|---|---|
| Subject | how to return data from insert into ... on conflict ... returning ... into |
| Date | |
| Msg-id | CAKXe9UAuza2LTAJm_rH7JtXxSLg8mSfTMOfqLjmUwsDQCK7JUQ@mail.gmail.com Whole thread Raw |
| Responses |
Re: how to return data from insert into ... on conflict ... returning ... into
|
| List | pgsql-general |
drop table if exists tbl;
drop sequence if exists seq;
create sequence seq;
create table tbl(
id int8 not null primary key,
d bytea not null,
h bytea not null, -- hash of the data, calculated automatically
dummy byte default 0 -- dummy value, see below...
);
alter table tbl add constraint uidx_tbl_h unique(h);
create or replace function trg() returns trigger language plpgsql as
$function$
begin
new.h = sha256(new.d); -- auto-hash
if new.id is null then
new.id = nextval('seq');
end if;
return new;
end;
$function$;
create trigger trg before insert or update on tbl for each row execute procedure trg();
do
$body$
declare
aid int8;
begin
insert into tbl(d) values ('1') returning id into aid; -- ok
raise notice '1->%', aid; -- ok
insert into tbl(d) values ('2') returning id into aid; -- ok
raise notice '2->%', aid; -- ok
insert into tbl(d) values ('1') on conflict(h) do update set dummy=0 returning id into aid; -- ok;
raise notice '1->%', aid; -- ok
insert into tbl(d) values ('2') on conflict(h) do update set dummy=0 returning id into aid; -- ok;
raise notice '2->%', aid; -- ok
end;
$body$;
delete from tbl;
do
$body$
declare
aid int8;
begin
insert into tbl(d) values ('1') returning id into aid; -- ok
raise notice '1->%', aid; -- ok
insert into tbl(d) values ('2') returning id into aid; -- ok
raise notice '2->%', aid; -- ok
insert into tbl(d) values ('1') on conflict(h) do nothing returning id into aid; -- ok;
raise notice '1->%', aid; -- null ??????
insert into tbl(d) values ('1') on conflict(h) do nothing returning id into aid; -- ok;
raise notice '2->%', aid; -- null ??????
end;
$body$;
delete from tbl;
do
$body$
declare
aid int8;
begin
insert into tbl(d) values ('1') returning id into aid; -- ok
raise notice '1->%', aid; -- ok
insert into tbl(d) values ('2') returning id into aid; -- ok
raise notice '2->%', aid; -- ok
select id from tbl into aid where h = sha256('1');
if not found then
insert into tbl(d) values ('1') on conflict(h) do update set id=id+0 returning id into aid;
end if;
raise notice '1->%', aid; -- null ??????
select id from tbl into aid where h = sha256('2');
if not found then
insert into tbl(d) values ('2') on conflict(h) do update set id=id+0 returning id into aid;
end if;
raise notice '2->%', aid; -- null ??????
end;
$body$;
pgsql-general by date: