Creating a trigger function in PostgreSQL

Question

I'm creating a trigger function that must populate a field called "numero_cip". This field must be fill just one time after a "INSERT ROW", because this field is a join from: "BR" + ID FIELD + SUFIXO FIELD.

So, i'm trying to create this trigger function:

CREATE OR REPLACE FUNCTION numeradora_cip() 
RETURNS trigger AS $$
DECLARE 
 sufixo varchar(2);
 numero_cip varchar(60);
BEGIN 
    sufixo := select p.sufixo from produto p where p.id = NEW.id_produto;
    NEW.numero_cip = select 'BR' || lpad(NEW.id, 11, '0') || sufixo;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

But i got the error:

ERROR:  syntax error at or near "select"
LINE 7:     sufixo := select p.sufixo from produto p where p.id = NE...

PS: This is my first time creating a trigger function.

Answer 1

From the fine manual:

40.5.3. Executing a Query with a Single-row Result

The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause. For example,

SELECT select_expressions INTO [STRICT] target FROM ...;
...

So you're looking for:

select p.sufixo into sufixo from produto p where p.id = NEW.id_produto;

And then, since your PL/pgSQL, you can do a simple string concatenation to get your numero_cip:

NEW.numero_cip := 'BR' || lpad(NEW.id, 11, '0') || sufixo

Answer 2

CREATE OR REPLACE FUNCTION numeradora_cip() 
RETURNS trigger AS $$
DECLARE 
 sufixo varchar(2);
 numero_cip varchar(60);
BEGIN 
    select p.sufixo into sufixo from produto p where p.id = NEW.id_produto;
    NEW.numero_cip := select 'BR' || lpad(NEW.id, 11, '0') || sufixo;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';