Reputation: 1045
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.
Upvotes: 0
Views: 118
Reputation: 236
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';
Upvotes: 0
Reputation: 434965
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
Upvotes: 1