How to check if OLD column exist in Postgres Trigger Function-CodePudding

I want to create a deleted logs and insert data from the OLD row column. The problem is the column is not same for each table, some tables only has transaction_date and other table only has created_at. So I want to check if transaction_date just use it, otherwise use created_at column. I tried using coalesce function but still return:

ERROR: record "old" has no field "transaction_date" CONTEXT: SQL statement "INSERT INTO "public"."delete_logs" ("table", "date") VALUES (TG_TABLE_NAME, coalesce(OLD.transaction_date, coalesce(OLD.created_at, now())))" PL/pgSQL function delete_table() line 2 at SQL statement

here is my function:

CREATE OR REPLACE FUNCTION delete_table() RETURNS trigger AS
$$BEGIN
INSERT INTO "public"."deleted_logs" ("table", "created_at") VALUES (TG_TABLE_NAME, coalesce(OLD.transaction_date, coalesce(OLD.created_at, now())));

   RETURN OLD;
END;$$ LANGUAGE plpgsql;


CREATE TRIGGER "testDelete" AFTER DELETE ON "exampletable" FOR EACH ROW EXECUTE PROCEDURE "delete_table"();

Actually, I wanted to create a function for each table, but I think it will be difficult to update the function in the future, so I need to create a single function for all tables.

CodePudding user response：

If you only have two alternative columns transaction_date and created_at, then you can try to manage the ERROR with an exception handling :

CREATE OR REPLACE FUNCTION delete_table() RETURNS trigger AS
$$BEGIN
INSERT INTO "public"."deleted_logs" ("table", "created_at") VALUES (TG_TABLE_NAME, OLD.transaction_date);
RETURN OLD;

EXCEPTION WHEN OTHERS THEN
INSERT INTO "public"."deleted_logs" ("table", "created_at") VALUES (TG_TABLE_NAME, OLD.created_at);
RETURN OLD ;

END;$$ LANGUAGE plpgsql;

CodePudding user response：

So I want to check if transaction_date just use it, otherwise use created_at column.

You can avoid the exception you saw by converting the row to json:

CREATE OR REPLACE FUNCTION log_ts_after_delete()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO public.deleted_logs
          (table_name   , created_at)  -- "table" is a reserved word
   VALUES (TG_TABLE_NAME, COALESCE(to_json(OLD)->>'transaction_date', to_json(OLD)->>'created_at')::timestamptz);


   RETURN NULL;  -- not used in AFTER trugger
END
$func$;

My answer assumes that transaction_date is defined NOT NULL. Else, the expression defaults to created_at. Probably not what you want.

JSON is not as strict as SQL. A reference to a non-existing JSON key results in NULL instead of the exception for the reference to a non-existing table column. So COALESCE just works.

How to set value of composite variable field using dynamic SQL

If the row is wide, it might be cheaper to convert to JSON only once and save it to a variable, or do it in a subquery or CTE.

To convert from Python arrays to PostgreSQL quickly?

If tables never switch the columns in question, passing a parameter in the trigger definition would be much cheaper. You find out (at trigger creation time) once with:

SELECT attname
FROM   pg_attribute
WHERE  attrelid = 'public.exampletable'::regclass
AND    attname IN ('transaction_date', 'created_at')
AND    NOT attisdropped 
ORDER  BY attname DESC

This returns 'transaction_date' if such a column exists in the table, else 'created_at', else NULL (no row). Related:

PostgreSQL rename a column only if it exists

It's still cheapest to have a separate trigger function for each type of trigger. Just two functions instead of one. If the trigger is fired often I would do that.

Avoid exception handling if you can. The manual:

Tip

A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.