CODEWITHSUNDEEP
postgresql
Dilshan
Dilshan

Reputation: 3001

postgresql CONCAT function error when use in a trigger

This might be a stupid question but pardon me, I'm trying to convert one of my MariaDB database into a PostgreSQL database. Here I'm getting an error while executing this function. I cannot find what's wrong here,

create function tg_prodcut_insert()
returns trigger as '
    BEGIN
        SET NEW.id = CONCAT(1, LPAD(INSERT INTO product_seq VALUES (NULL) returning id, 6, 0));      
    END;
' LANGUAGE 'plpgsql';

Error is pointing to the 1 in CONCAT method, The type of id I'm trying to SET is char(7)

EDIT I also tried this, this won't work either,

create function tg_orders_insert()
returns trigger as '
    BEGIN
        INSERT INTO order_seq VALUES (NULL);
        SET NEW.id = CONCAT('1', LPAD(LAST_INSERT_ID(), 6, 0));       
    END;
' LANGUAGE 'plpgsql';

Thanks in advance.

Upvotes: 1

Views: 306

Answers (1)

user330315
user330315

Reputation:

It seems you are trying to simulate some kind of sequence with that code by inserting into a table and then getting the auto_increment value from that.

This can be done much more efficiently using a sequence in Postgres.

The error you get also isn't caused by the concat() function but because you are using the wrong syntax.

Value assignment is done using := in PL/pgSQL.

And there is also no last_insert_id() function in Postgres. To get the next value from a sequence use nextval(), to get the most recently generated value, you can use lastval() but that's not necessary here.

create sequence product_id_seq;

create function tg_product_insert()
returns trigger as 
$$
BEGIN
  NEW.id := concat('ORD', to_char(nextval('product_id_seq'), 'FM00000000'));
  return new;
END;
$$
LANGUAGE plpgsql;

you will need to create a before trigger for that to work:

create trigger product_seq_trigger
  before insert on product
  for each row
  execute procedure tg_product_insert();

Online example

But it would be a lot more efficient to switch to a proper identity column instead and get rid of the trigger.

Upvotes: 3

Related Questions