Postgresql trigger function with concatenate not working-CodePudding

I'm new to trigger functions and PostgreSQL, and I'm trying create a calculated field combining other fields (I know that this might violate the normal forms, but I'm working with a pre-existing naming convention). The goal is to have the animal_id field be created at insertion or update of records and look like "ANAM-2011-10". I have tried two different ways to insert a concatenated string, and each has their own issues. Here's the table:

CREATE TABLE capt_trial
(animal_id VARCHAR(15),
species CHAR(4),
capture_year SMALLINT,
tag_num NUMERIC,
tag_col CHAR(1));

Scenario #1: using SET field = concatenated string in function

CREATE OR REPLACE FUNCTION create_short_animal_id_1()
RETURNS trigger
LANGUAGE plpgsql
AS
$$
BEGIN
    SET NEW.animal_id = CONCAT(species, '-', capture_year, '-', tag_num);
    RETURN NEW;
END;
$$;

This is the error message when trying to create the function:

ERROR:  syntax error at or near "("
LINE 7:  SET NEW.animal_id = CONCAT(species, '-', capture_year, '-',...
                                   ^
SQL state: 42601
Character: 125

Scenario #2: using INSERT INTO field VALUES concatenated string in function. The function and the trigger were created successfully.

CREATE OR REPLACE FUNCTION create_short_animal_id_2()
RETURNS trigger
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO capt_trial(animal_id) 
    VALUES(CONCAT(species, '-', capture_year, '-', tag_num));
    RETURN NEW;
END;
$$;


CREATE TRIGGER trigger_create_short_animal_id_2
BEFORE INSERT OR UPDATE
ON public."capt_trial"
FOR EACH ROW
EXECUTE PROCEDURE create_short_animal_id_2();

But when I tried to insert values into the table:

INSERT INTO capt_trial (species, capture_year, tag_num, tag_col)
VALUES ('ANAM', 2011, 10, 'B'),
       ('ANAM', 2012, 142, 'Y'),
       ('OVCA', 2013, 137, NULL),
       ('ODHE', 2014, 75, NULL);

I got this error:

ERROR:  column "species" does not exist
LINE 2:  VALUES(CONCAT(species, '-', capture_year, '-', tag_num))
                       ^
HINT:  There is a column named "species" in table "capt_trial", but it cannot be referenced 
from this part of the query.
QUERY:  INSERT INTO capt_trial(animal_id) 
        VALUES(CONCAT(species, '-', capture_year, '-', tag_num))
CONTEXT:  PL/pgSQL function create_short_animal_id() line 3 at SQL statement
SQL state: 42703

Does anyone know what the issue is with the syntax or how to get this to run?

CodePudding user response：

For first example do:

NEW.animal_id = CONCAT(NEW.species, '-', NEW.capture_year, '-', NEW.tag_num)

You are assigning to the NEW.animal_id not setting anything. FYI, := can also be used for assignment.

CodePudding user response：

CREATE OR REPLACE FUNCTION create_short_animal_id_2()
RETURNS trigger
LANGUAGE plpgsql
AS
$$
BEGIN
    NEW.animal_id := concat(NEW.species,'-',NEW.capture_year)
    RETURN NEW;
END;
$$;


CREATE TRIGGER trigger_create_short_animal_id_2
BEFORE INSERT OR UPDATE
ON public."capt_trial"
FOR EACH ROW
EXECUTE PROCEDURE create_short_animal_id_2

You can use above draft trigger and func to have your result.NEW- record in new result, OLD - record before trigger function.