have issue using substring with postgres trigger here is the trigger i used

CREATE FUNCTION TRIGGER1() RETURNS trigger AS $autouuid$
BEGIN

    update test_points.scada_rtu i
    set dummy #field_name
    = upper(substr (NEW.city_name, 1, 2) || right (NEW.city_name, 1) || '-' || substr  (NEW.phase_name, 1, 2) || right (NEW.phase_name, 1) || '-' || substr (NEW.area_name, 1, 3) || '-' || 
            substr(NEW.name, 1, 2) || right (NEW.name, 1) || '-' || substr (NEW.rtu_model, 1, 2) || right (NEW.rtu_model, 1)) 
    WHERE i.id = OLD.id;
    RETURN NEW;
END;
$autouuid$ LANGUAGE plpgsql;
CREATE TRIGGER autouuid_update BEFORE INSERT OR UPDATE ON test_points.scada_rtu
    FOR EACH ROW
EXECUTE PROCEDURE public.TRIGGER1();

error message

CodePudding user response：

CREATE FUNCTION TRIGGER1() RETURNS trigger AS $autouuid$
BEGIN
    NEW.dummy=upper(
                concat_ws('-',
                    regexp_replace(NEW.city_name,   '^(..).*(.)$',  '\1\2'),
                    regexp_replace(NEW.phase_name,  '^(..).*(.)$',  '\1\2'),
                    regexp_replace(NEW.name,        '^(..).*(.)$',  '\1\2'), 
                    regexp_replace(NEW.rtu_model,   '^(..).*(.)$',  '\1\2')
                )
              ); 
    RETURN NEW;
END;
$autouuid$ LANGUAGE plpgsql;
CREATE TRIGGER autouuid_update BEFORE INSERT OR UPDATE ON test_points.scada_rtu
    FOR EACH ROW
EXECUTE PROCEDURE public.TRIGGER1();

1. As pointed out by @a_horse_with_no_name, you can assign the new values to NEW record directly, without issuing a new update.
2. Issuing a new update from a function triggered on updates makes it call itself, putting you in an infinite loop, as noted by @Frank Heikens.
3. Direct assignment to record-type variable NEW will solve your problem, because it'll operate within trigger's context where trigger's special variables are visible. Your error is caused by the fact your update query thinks it's a reference to one of the target table's columns, ignoring the variable. @Adrian Klaver
4. You don't need to repeatedly concatenate and manually add the separator each time using ||. concat_ws() does both of these things for you, also ignoring NULL values.
5. Getting the first two characters and the last character of a string can also be done with a single regexp_replace().
6. #comment is not a valid PL/pgSQL comment. Use --comment for single-line end-of-line or /*comment*/ for multi-line, mid-line comments. Your comments will be saved with the function definition and visible whenever someone looks it up.