PostgreSQL - Trigger on INSERT or UPDATE

Question

I wan't to add a trigger in a PostgreSQL database. This trigger is used to concatenate values of 2 columns to update a 3rd one. I wan't to run it when a row is inserted or updated in the table.

Table

CREATE TABLE IF NOT EXISTS FILE(ID INT NOT NULL PRIMARY KEY, FOLDER TEXT, NAME TEXT, URL TEXT);

Function

CREATE OR REPLACE FUNCTION LINK() RETURNS trigger AS 
$$
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    UPDATE FILE
    SET URL = CONCAT(FOLDER, NAME)
    WHERE ID = OLD.ID;
  ELSIF (TG_OP = 'INSERT') THEN
    UPDATE FILE   
    SET URL = CONCAT(FOLDER, NAME)
    WHERE ID = NEW.ID;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE PLPGSQL;

Trigger

CREATE TRIGGER TRIGGER_LINK
BEFORE INSERT OR UPDATE 
ON FILE
FOR EACH ROW 
EXECUTE PROCEDURE LINK();

When I insert a value in table like

INSERT INTO FILE VALUES (1, 'C:\', 'doc.pdf');

I have an error message list index out of range because ID number is not yet created and UPDATE query on INSERT can't execute. But if I make an AFTER UPDATE it will run infinitely.

How to run a trigger function on INSERT or UPDATE with a WHERE clause on ID to target only inserted or updated row ? I'm using PostgreSQL 10.14.

score 5 · Accepted Answer · 2021-01-20 13:33:56Z

5

Don't use UPDATE to do that. Just assign the value. Also, a BEFORE trigger should not return null because that will abort the operation.

CREATE OR REPLACE FUNCTION LINK() RETURNS trigger AS 
$$
BEGIN
  new.url := CONCAT(new.FOLDER, new.NAME);
  RETURN new; --<< important!
END
$$
LANGUAGE PLPGSQL;

edited Jan 20, 2021 at 13:33

answered Jan 20, 2021 at 13:17

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

GeoGyro Over a year ago

All right, I understand know. Simple and efficient solution

Collectives™ on Stack Overflow

PostgreSQL - Trigger on INSERT or UPDATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related