I have two tables:
CREATE TABLE "user"
(
username character varying(35) NOT NULL,
CONSTRAINT user_pk PRIMARY KEY (username)
)
CREATE TABLE item
(
id serial NOT NULL,
username character varying(35),
user_item_number integer,
item_value character varying(35),
CONSTRAINT item_pk PRIMARY KEY (id),
CONSTRAINT item_fk FOREIGN KEY (username)
REFERENCES "user" (username) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT unique_item_username UNIQUE (username, user_item_number)
)
I would like to auto increment user_item_number
separately for each username
. Following image shows example. For each username
: (user1
, user2
) user_item_number
starts form 1 and is incremented by 1.
I suppose that I should use some trigger before insert which get max value of user_item_number
for username
which is inserted and increment it. But I don't know how to write that trigger.
I also don't know how to consider a concurency access (concurency insert more than one row with the same value of username
). I don't want to get constraint violation error when two rows with the same username
and user_item_number
are inserted, I would like that trigger catch that error and one again increment user_item_number
value.
Any idea?
I found solution. I wrote trigger and procedure:
create OR REPLACE function myinsert() RETURNS trigger as $$
BEGIN
if NEW.user_item_number is not null then return NEW;
end if;
loop
<<roolbac_to>>
declare
max INTEGER:=null;
begin
SELECT count(user_item_number) into max from item where username=NEW.username;
if max is null then
max:=1;
ELSE
max=max+1;
end if;
INSERT INTO item( username, user_item_number, item_value) VALUES (NEW.username,max, NEW.item_value);
exit;
exception WHEN unique_violation THEN
--do nothing
end;
end loop;
return null;
end;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER trig1
before insert
ON item
FOR EACH ROW
EXECUTE PROCEDURE myinsert();
This sulution alows for gaps creation, but it is ok for me.
I wanted to moke trigger instead of insert but it is impossible. So I made before insert trigger and return null. Insert is executed inside procedure. Instruction:
if NEW.user_item_number is not null then return NEW;
end if;
is to not allow for reccurence