Question 114516 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql trigger function with concatenate not working

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search