Function Postgresql

Asked

Viewed 81 times

0

I need to create a Function() in my bank PostgreSQL so that whenever there is a movement of INSERT or UPDATE this function is activated in a given table through a TRIGGER so that in my log table are stored all the movements that the product has suffered. I created the following Function:

CREATE OR REPLACE FUNCTION public.atualiza_suprimentoslog()
RETURNS trigger
AS
$$
begin
    IF (TG_OP = 'UPDATE') then
        IF (NEW.numeroserie <> OLD.numeroserie) THEN
            INSERT INTO suprimentoslog (codigoestoque, numeroserie, dataoperacao, clienteempresa, clienteusuario, solicitante, operacao)
            values
            (new.codigoestoque, new.numeroserie, current_date, ' - ', ' - ', ' - ', 'ALTERACAO SUPRIMENTO') 
            return new;
        END IF;
    end if;
    if (TG_OP = 'INSERT') then
        INSERT INTO suprimentoslog (codigoestoque, numeroserie, dataoperacao, clienteempresa, clienteusuario, solicitante, operacao)
        values
        (new.codigoestoque, new.numeroserie, current_date, ' - ', ' - ', ' - ', 'ENTRADA SUPRIMENTO') 
        return new;
    end if;
    return null;
end
$$  LANGUAGE plpgsql

But the same returns me the following error when I try to execute it:

ERROR:  syntax error at or near "return"
LINE 11:    return new;
            ^
********** Error **********

ERROR: syntax error at or near "return"
SQL state: 42601
Character: 411
postgresql function

1 answer

1


Missing a semicolon at the end of INSERT. Ex:

INSERT INTO suprimentoslog (
codigoestoque
, numeroserie
, dataoperacao
, clienteempresa
, clienteusuario
, solicitante
, operacao)
values
(new.codigoestoque
, new.numeroserie
, current_date
, ' - '
, ' - '
, ' - '
, 'ALTERACAO SUPRIMENTO'); --Aqui

Browser other questions tagged postgresql function

You are not signed in. Login or sign up in order to post.