Error in PostgreSQL trigger

Question

i'm coding this trigger in postgreSQL

CREATE OR REPLACE FUNCTION fn_trg_viabilidad_fila()
RETURNS trigger AS
$BODY$
BEGIN
PERFORM S.* 
FROM MontoMinimo M, SolicitudPresupuesto S, Cantidad C, Producto P
WHERE P.idProducto=C.idProducto
and C.idPresupuesto=S.idPresupuesto
and M.idMonto=S.idMonto;
IF (C.cantidad < P.canMinExp OR P.exportable = FALSE)
THEN 
UPDATE SolicitudPresupuesto
SET viable = FALSE
WHERE idPresupuesto = OLD.idPresupuesto;
RETURN NEW;
END IF;
END
$BODY$
LANGUAGE plpgsql

CREATE TRIGGER trg_viabilidad_fila BEFORE INSERT
OR UPDATE ON SolicitudPresupuesto
FOR EACH ROW EXECUTE PROCEDURE
fn_trg_viabilidad_fila() ;

I can't solve this error..

An error has occurred: ERROR: missing FROM-clause entry for table "c" LINE 1: SELECT C.cantidad < P.canminexp OR P.exportable = FALSE ^ QUERY: SELECT C.cantidad < P.canminexp OR P.exportable = FALSE CONTEXT: PL/pgSQL function fn_trg_viabilidad_fila() line 9 at IF

I will be very grateful to any help. Sorry for my bad english

user330315 · Accepted Answer · 2015-07-25 07:00:34Z

1

You can't access the columns of a query outside of the query (or the block where you use the query). You need to store the result of the select somewhere. Additionally you shouldn't run an UPDATE on the triggered table, you need to assign the value to the NEW record.

CREATE OR REPLACE FUNCTION fn_trg_viabilidad_fila()
  RETURNS trigger AS
$BODY$
DECLARE
  l_result boolean;
BEGIN

  SELECT (c.cantidad < p.canMinExp OR p.exportable = FALSE)
    INTO l_result
  FROM MontoMinimo M
    JOIN SolicitudPresupuesto s ON m.idMonto = s.idMonto
    JOIN Cantidad c ON c.idPresupuesto = s.idPresupuesto
    JOIN Producto p ON p.idProducto = c.idProducto;

  IF l_result THEN
    new.viable := false; 
  END IF;

  RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

It would be possible to "inline" the query into the IF statement but this way it resembles the structure of your current code better. Also note that I replaced the old, outdated implicit joins by an explicit and more robust JOIN operator.

The assigment new.viable assumes that idpresupuesto is the PK in the table solicitudpresupuesto (because you used that in the WHERE clause of the UPDATE statement)

answered Jul 25, 2015 at 7:00

user330315

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

1 Comment

Colo Müller Over a year ago

Thank you very much! I am new to programming in this code, and I'm learning. I thank you again!

Collectives™ on Stack Overflow

Error in PostgreSQL trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related