PostgreSQL 'BEFORE INSERT' TRIGGER Problem with QGIS

Question

In PostgreSQL I created a simple PL/pgSQL trigger function like this:

CREATE OR REPLACE FUNCTION set_rv_default_farben() RETURNS trigger AS 
$$
BEGIN
 IF NEW.parent_rv IS NULL THEN
 IF NEW.anz_rohre = 12 THEN
 NEW.farben := '{1,2,3,4,5,6,7,8,9,10,11,12}';
 ELSIF NEW.anz_rohre = 24 THEN
 NEW.farben :='{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24}';
 END IF;
 END IF;
END;
$$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER rv_farben_def_trig BEFORE INSERT ON rohrverbund EXECUTE PROCEDURE set_rv_default_farben();

In QGIS, when I add a feature to layer rohrverbund with attribute parent_rv left NULL as well as anz_rohre set to 12 and try to save edits for this layer I get an error message like (partial in german, but I try to translate ...)

Could not commit changes to layer rohrverbund
Errors: ERROR: 1 feature(s) not added.
 Provider errors:
 PostGIS error while adding features: FEHLER: Record »new« hat noch keinen Wert [... has no value]
 DETAIL: Die Tupelstruktur eines Records ohne Wert ist unbestimmt. [tuple structure of a record with no value is undetermined]
 CONTEXT: PL/pgSQL-Funktion set_rv_default_farben() Zeile 3 bei IF [line 3 at IF]

What am I missing? The recommendation and all examples I found use BEFORE INSERT and NEW.attribute, better use AFTER INSERT and OLD.attribute?

Answer 1

There are two issues. First, the one that produce the error, the trigger must specify that you are applying it for each row. Secondly, the function must return New otherwise nothing will be inserted in the DB.

CREATE OR REPLACE FUNCTION set_rv_default_farben() RETURNS trigger AS 
$$
BEGIN
 IF NEW.parent_rv IS NULL THEN
 IF NEW.anz_rohre = 12 THEN
 NEW.farben := '{1,2,3,4,5,6,7,8,9,10,11,12}';
 ELSIF NEW.anz_rohre = 24 THEN
 NEW.farben :='{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24}';
 END IF;
 END IF;
 RETURN NEW;
END;
$$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER rv_farben_def_trig 
BEFORE INSERT ON rohrverbund 
FOR EACH ROW 
EXECUTE PROCEDURE set_rv_default_farben();