I will understand with trigger functions. For this, first a simple table was created and a function was added:

CREATE TABLE AplusB (A integer, B integer); INSERT INTO AplusB VALUES (1,1); INSERT INTO AplusB VALUES (2,2); INSERT INTO AplusB VALUES (3,3); CREATE FUNCTION plus(integer, integer) RETURNS integer LANGUAGE SQL AS 'SELECT $1 + $2;';

After that, another label and a trigger function are created:

 CREATE TABLE ABresult (result integer); CREATE FUNCTION ABsumm() returns TRIGGER AS 'BEGIN DELETE FROM ABresult; INSERT INTO ABresult VALUES (AplusB.A+AplusB.B); RETURN NULL; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER makeABresult AFTER INSERT OR UPDATE OR DELETE ON AplusB FOR EACH STATEMENT execute procedure ABsumm();

After trying to insert:

 INSERT INTO AplusB VALUES (100,200);

An error occurs:

ERROR: missing FROM clause entry for table "aplusb" STRING 1: INSERT INTO ABresult VALUES (AplusB.A + AplusB.B) ^ REQUEST: INSERT INTO ABresult VALUES (AplusB.A + AplusB.B) CONTEXT: PL / pgSQL function "absumm" line 3 at SQL statement

What could be the problem?

(Source of examples: wiki.linuxformat.ru/wiki/LXF87-88:PostgreSQL)

  • PostgreSQL has no idea how to find AplusB.A+AplusB.B , because AplusB does not appear in your query. - etki
  • How not to appear? I specify which table: INSERT INTO AplusB VALUES (100,200) - ReD
  • and what is AplusB.A? - etki 2:53 pm

1 answer 1

The point is probably that Postgres cannot understand what exactly it needs to add in the function on the 3rd line. When a trigger function is called, a context is passed in which it can be accessed. In your case, as far as I understand, you need to get the values, from the insertion / update of which the trigger worked.

If so, try replacing in function:

INSERT INTO ABresult VALUES (AplusB.A+AplusB.B);

on:

INSERT INTO ABresult VALUES (NEW.A + NEW.B);

More information about the context can be found here.

  • Helpful replacement of INSERT INTO ABresult VALUES AplusB.A+AplusB.B); on INSERT INTO ABresult ( result ) SELECT A + B FROM AplusB; - ReD