How do I change a trigger in MySql for PostgreSQL?

Question

I've tried to change this trigger code from MySQL to PostgreSQL syntax.

In MySQL look like this:

delimiter $$
create trigger trggBeforeInsertTUser before insert on TUsuario FOR EACH ROW
begin
set @LastCod=(select max(codUsr) from TUsuario);
if @LastCod is null then
   set @LastCod="USRX0000000";
end if;
set @partText=mid(@LastCod, 1, 8);
set @partNum=mid(@LastCod, 9, 7)+1;
set @longNumber=(select length(@partNum));
set @codNum=concat(repeat('0', 7-@longNumber), @partNum);
set @codNum=concat(@partText, @codNum);
set NEW.codUsr=(select @codNum);
end

In Postgres I tried this:

CREATE TRIGGER trggBeforeInsertTUser BEFORE INSERT on TUser 
  EXECUTE PROCEDURE sp_incremental();

CREATE OR REPLACE FUNCTION sp_incremental () RETURNS TRIGGER AS trggBeforeInsertTUser
BEGIN
set LastCod=(select max(codUser) from TUser);
if LastCod is null then
    set LastCod="USRX0000000";
end if;
set partText=mid(LastCod, 1, 8);
set partNum=mid(LastCod, 9, 7)+1;
set longNumber=(select length(partNum));
set codNum=concat(repeat('0', 7-longNumber), partNum);
set cod=concat(partText, codNum);
set NEW.codUsr=(select cod);
END

But without success. Any help??

What error are you getting?

Dzyann
– Dzyann

2015-02-19 15:21:54 +00:00
Commented Feb 19, 2015 at 15:21 — Dzyann
– Dzyann, Commented Feb 19, 2015 at 15:21

Bruno Ribeiro · Accepted Answer · 2015-02-19 15:45:17Z

2

Well, you have some problems in syntax to PostgreSQL. I recommend you read this: http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html

You can try something like this, just compile, test and see if it's ok:

CREATE OR REPLACE FUNCTION sp_incremental() RETURNS TRIGGER AS $sp_incremental$
DECLARE 
    LastCod character varying(100);
    partText character varying(100);
    partNum character varying(100);
    longNumber bigint;
    codNum character varying(100);
    cod character varying(100);
BEGIN
    LastCod := (select max(codUser) from TUser);
    if LastCod is null then
        LastCod := "USRX0000000";
    end if;
    partText := mid(LastCod, 1, 8);
    partNum := mid(LastCod, 9, 7) + 1;
    longNumber := (select length(partNum));
    codNum := concat(repeat('0', 7 - longNumber), partNum);
    cod := concat(partText, codNum);
    NEW.codUsr := (select cod);
END;
$sp_incremental$ LANGUAGE plpgsql;

CREATE TRIGGER trggBeforeInsertTUser BEFORE INSERT on TUser 
  EXECUTE PROCEDURE sp_incremental();

Also, set data types for your needs.

answered Feb 19, 2015 at 15:45

Bruno Ribeiro

6,2956 gold badges43 silver badges49 bronze badges

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

2 Comments

greg dorian Over a year ago

well done!! bruno, you just delete the "set" phrase, and it works fine!

greg dorian Over a year ago

one little correction I use + for concatenate (in mysql) || and mid replace for substr(x,a,b)

Collectives™ on Stack Overflow

How do I change a trigger in MySql for PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related