PostgreSQL with unavailable parameter in function

Question

I have this function built using PostgreSQL:

CREATE OR REPLACE FUNCTION "db"."t_pencabutan_likuidasi_after_update"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
BEGIN
    IF(NEW.status <> OLD.status) THEN
        INSERT INTO 
             t_pencabutan_likuidasi_log(id_pencabutan, id_profile, nama_petugas, nip_petugas, status, catatan)
        VALUES(
             NEW.id_pencabutan, NEW.id_profile, NEW.nama_petugas, NEW.nip_petugas, NEW.status, NEW.catatan);
    END IF;
        RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

Above is a function that will triggered BEFORE UPDATE of the table liquidator.

The problem is some of the fill on the INSERT were not mandatory inserted, like nama_petugas and nip_petugas which is a temporary value. Sometimes it is inserted, but sometimes it isn't. It gives an error like this:

ERROR:  record "new" has no field "nama_petugas"

How could I leave it blank when there is no value/variable that is inserted for nama_petugas and nip_petugas?

Here is my trigger function;

CREATE TRIGGER 
"t_pencabutan_likuidasi_after_update" 
AFTER UPDATE OF "status" 
ON "db"."liquidator"
FOR EACH ROW
EXECUTE PROCEDURE 
"db"."t_pencabutan_likuidasi_after_update"();

The error message indicates that the table for which the trigger is defined has no column with the name `"nama_petugas"`. Please **[edit]** your question and add the `create table` for the table in question. And `create trigger` statement might be helpful as well — , Mar 26 '19 at 20:15

score 1 · Accepted Answer · answered Mar 27 '19 at 07:30

1

The variable NEW.nama_petugas exists if and only if the table on which the trigger is defined has a column of that name.

You cannot use this trigger with different tables if some of them don't have a column of that name.

Either write several trigger functions or ose a trick like in this answer to use dynamic SQL.

answered Mar 27 '19 at 07:30

Laurenz Albe

209,280
17
206
263

yes, the updated table liquidator` had `nama_petugas` column, but when i tried to update the `liquidator` table for certain value only, i didnt include `nama_petugas` column on that UPDATE query for table `liquidator`... – Gagantous Mar 27 '19 at 08:59
okay i have edited my question, so i have to do like this ? IF NEW.nama_petugas = "" THEN nama_petugas = '' ? is it right ? – Gagantous Mar 27 '19 at 09:09
eh, sorry, there is no column `nama_petugas` in table `liquidator` – Gagantous Mar 27 '19 at 09:14
Right, and that is the problem. – Laurenz Albe Mar 27 '19 at 09:22

PostgreSQL with unavailable parameter in function

1 Answers1