Postgresql – missing FROM-clause entry for table in trigger function in postgresql

auditpostgresqltrigger

I have created an audit trigger for table "product" that should insert data into "log_product".

CREATE FUNCTION public.insert_log_product()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF 
ROWS 0
AS $BODY$
BEGIN
IF (TG_OP = 'UPDATE')
THEN
    INSERT INTO log_table(
      operation,
      product_type_new, 
      product_type_old,
      product_name_new,
      product_name_old,
      vendor_id,
      available_quantity_unit_new,
      available_quantity_unit_old,
      delivery_time_in_days_new,
      delivery_time_in_days_old,
      change_date
    )
    VALUES(
      'UPDATE',
      new.product_type,
      old.product_type,
      new.product_name,
      old.product.name,
      new.vendor_id,
      old.vendor_id
      new.available_quantity_unity,
      old.available_quantity_unity,
      new.delivery_time_in_days,
      old.delivery_time_in_days,
      now()
    );

END IF;
RETURN NULL;
END;

 $BODY$;

But I get an error message:

org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "product"

The function is created using PGadmin 4.2.

CREATE TRIGGER insert_log_product_trigger
AFTER INSERT OR DELETE OR UPDATE 
ON public.table
FOR EACH ROW
EXECUTE PROCEDURE public.insert_log_product();

The requirement for our audit table is to have each column separately, old and new.

Best Answer

You are trying to insert 42 values into 41 columns. Check vendor_legal_id.