CODEWITHSUNDEEP
sqlpostgresqltriggers
puk
puk

Reputation: 16762

How to collect information from other tables inside PostgreSQL triggers

I have a Filing Cabinet table which references a Drawer table. I want to auto generate labels for their rows (see MWE below). For example, a filing cabinet on the third row, and in the first room would be 03:01 and its drawer on the second row, first column would be 03:01:02:01. I know how to do this at the interface level (Python/PHP), but would like to achieve this at the database level. Is this possible advisable? 

CREATE TABLE Drawer(
   id SERIAL PRIMARY KEY,
   drawerCol INT,
   drawerRow INT,
   label Varchar(256)
);

CREATE TABLE FilingCabinet(
   id SERIAL PRIMARY KEY,
   room INT,
   floor INT,
   label VARCHAR (256),
   drawerId INT REFERENCES Drawer (id) UNIQUE --Ensures two rooms don't share a drawer
);

CREATE OR REPLACE FUNCTION f_update_labels() RETURNS TRIGGER AS $update_labels$
BEGIN
   --set FilingCabinet label to be floor:room
   --set Drawer label to be floor:room:drawerRow:drawerCol
   if FALSE THEN
      NEW.id := OLD.id; --Avoid psql throwing an error
   END IF;
   RETURN NEW;
END; $update_labels$ LANGUAGE plpgsql;

CREATE TRIGGER update_labels
   BEFORE INSERT OR UPDATE ON FilingCabinet
   FOR EACH ROW
   EXECUTE PROCEDURE f_update_labels ();

INSERT INTO Drawer (drawerCol,drawerRow) VALUES (1,1);
INSERT INTO Drawer (drawerCol,drawerRow) VALUES (1,2);
INSERT INTO Drawer (drawerCol,drawerRow) VALUES (1,3);
INSERT INTO Drawer (drawerCol,drawerRow) VALUES (1,1);
INSERT INTO Drawer (drawerCol,drawerRow) VALUES (1,2);
INSERT INTO Drawer (drawerCol,drawerRow) VALUES (1,3);

INSERT INTO FilingCabinet (room,floor,drawerId) VALUES (1,1,1);
INSERT INTO FilingCabinet (room,floor,drawerId) VALUES (1,1,2);
INSERT INTO FilingCabinet (room,floor,drawerId) VALUES (1,1,3);
INSERT INTO FilingCabinet (room,floor,drawerId) VALUES (1,2,4);
INSERT INTO FilingCabinet (room,floor,drawerId) VALUES (1,2,5);
INSERT INTO FilingCabinet (room,floor,drawerId) VALUES (1,2,6);

Upvotes: 0

Views: 30

Answers (1)

Patrick
Patrick

Reputation: 32179

CREATE OR REPLACE FUNCTION f_update_labels() RETURNS TRIGGER AS $update_labels$
BEGIN
  --set FilingCabinet label to be floor:room
  NEW.label := to_char(NEW.floor, 'FM00') || ':' || to_char(NEW.room, 'FM00');

  --set Drawer label to be floor:room:drawerRow:drawerCol
  UPDATE drawer
  SET label = NEW.label || ':' || to_char(drawerrow, 'FM00') || ':' || to_char(drawercol, 'FM00')
  WHERE id = NEW.drawerid;

  RETURN NEW;
END; $update_labels$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions