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.

mu is too short · Accepted Answer · 2013-12-15 19:33:33Z

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

answered Dec 15, 2013 at 19:33

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shelly Over a year ago

But the error point to line 7, the line in "SELECT p.sufixo..."

mu is too short Over a year ago

Because you're saying sufixo := select ... when you should be saying select ... into sufixo ...

PlsqlDev · Accepted Answer · 2013-12-17 21:22:53Z

0

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';

answered Dec 17, 2013 at 21:22

PlsqlDev

2362 silver badges5 bronze badges

Collectives™ on Stack Overflow

Creating a trigger function in PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related