postgresql CONCAT function error when use in a trigger

Question

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.

Still not working. I also tried to_char(1, '9') and it also not working. — Dilshan
– Dilshan, Commented Aug 21, 2020 at 16:18
Hmm. What's wrong in LPAD ? It wasn't worked with LAST_INSERT_ID, so I moved the insert query in to the LPAD and returned the id. Is that wrong ? — Dilshan
– Dilshan, Commented Aug 21, 2020 at 16:21
I added the function I tried first. All I want is to get the last_insert_id. — Dilshan
– Dilshan, Commented Aug 21, 2020 at 16:26
You are looking for lastval() or currval() postgresql.org/docs/current/functions-sequence.html — user330315
– user330315, Commented Aug 21, 2020 at 16:26

score 3 · Accepted Answer · 2020-08-21 16:45:56Z

3

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.

edited Aug 21, 2020 at 16:45

answered Aug 21, 2020 at 16:36

user330315

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

5 Comments

Dilshan Over a year ago

Thank you for the answer but now it error is pointing to :=

user330315 Over a year ago

There were a few typos in there, but that is none of them.

Adrian Klaver Over a year ago

FYI, assignment can also be done with = per link above. What is the error message?

user330315 Over a year ago

@Dilshan: I fixed some typos, the code definitely works (now): dbfiddle.uk/…

Dilshan Over a year ago

@a_horse_with_no_name Thank you so much. It's working :)

Collectives™ on Stack Overflow

postgresql CONCAT function error when use in a trigger

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related