Creating a trigger in postgres

Question

I'm trying to create a trigger via pgadmin in order to have a calculated column.

I need to do: c = a / b

I created a function UpdateC, which contains the following code:

UPDATE table_name
SET c = a / b
WHERE id = @id

and I declared @id as a parameter.

Then I thought I would create a trigger and would link it to that function, but I found out that I need to link it to a trigger function rather than to a function.

Now I'm trying to create a trigger function with the exact same SQL as above, but I'm getting the next error:

syntax error at or near "UPDATE"

Under definition tab I had to choose a language, so I chose "plpgsql" (The other options are "c" and "internal").

Any help will be profoundly appreciated!

Answer 1

You should create a BEFORE trigger FOR EACH ROW and not UPDATE the table, but change the row before it is written.

See the doocumentation for details.

Here is a proof of concept:

CREATE TABLE t (
   id integer PRIMARY KEY,
   a double precision,
   b double precision,
   c double precision
);

CREATE OR REPLACE FUNCTION t_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.c = NEW.a / NEW.b;
   RETURN NEW;
END;$$;

CREATE TRIGGER t_trig BEFORE INSERT OR UPDATE ON t FOR EACH ROW
   EXECUTE PROCEDURE t_trig();

INSERT INTO t (id, a, b) VALUES (1, 42, 7);
INSERT 0 1
test=> SELECT * FROM t;
┌────┬────┬───┬───┐
│ id │ a  │ b │ c │
├────┼────┼───┼───┤
│  1 │ 42 │ 7 │ 6 │
└────┴────┴───┴───┘
(1 row)