1

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?

2

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.