CODEWITHSUNDEEP
postgresql
Patan
Patan

Reputation: 17893

AutoIncrement Value in Table for each Value on Other column

I am planning to write a audit table to have audited data for the items based on its version

I want to auto increment the version for each item id.

For example my data looks like this

CREATE TABLE item_audit (
    item_id         VARCHAR(10),
    version         INT not null,
    value           TEXT not null,
    CONSTRAINT item_audit_pkey PRIMARY KEY (item_id, version)

);

item_id         |   version       |   value              |
      1         |       1         |       value11        |
      1         |       2         |       value12        |
      1         |       3         |       value13        |
      1         |       4         |       value14        |
      2         |       1         |       value21        |
      2         |       2         |       value22        |
      3         |       1         |       value31        |
      4         |       1         |       value41        |

How do I make sure sure that version auto increments based on item_id Are there any inbuilt procedures in Postgres

Upvotes: 1

Views: 98

Answers (1)

Jim Jones
Jim Jones

Reputation: 19693

I believe a trigger might be what you're looking for.

First you need a function to increment your version based on item_id. Something like ..

CREATE OR REPLACE FUNCTION increment_version() RETURNS trigger AS
$BODY$
DECLARE i INTEGER;
BEGIN
  SELECT max(version) FROM item_audit 
  WHERE item_id = NEW.item_id INTO i;
  NEW.version = COALESCE(i,0)+1;
  RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';

Then attach this function to the table

CREATE TRIGGER check_version
BEFORE INSERT OR UPDATE ON item_audit
FOR EACH ROW EXECUTE PROCEDURE increment_version();

After that, everytime you insert a new record into item_audit the function will be executed:

INSERT INTO item_audit (item_id, value) VALUES 
(1,'value11'),(1,'value12'),(1,'value13'),(1,'value14'),
(2,'value21'),(2,'value22'),(3,'value31'),(4,'value41');

SELECT * FROM item_audit;

 item_id | version |  value  
---------+---------+---------
 1       |       1 | value11
 1       |       2 | value12
 1       |       3 | value13
 1       |       4 | value14
 2       |       1 | value21
 2       |       2 | value22
 3       |       1 | value31
 4       |       1 | value41
(8 Zeilen)

Upvotes: 1

Related Questions