hi, i´ve trying to change this trigger code from MySql to Postgres syntax
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
to postgres syntax
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
without success... any help??