how to properly implement soft delete in postgresql?

Question

in my postgres 9.3 database, I have the following combination of function and a trigger to implement soft delete functionality:

ALTER TABLE "LIBN02"."trigger_test_1"
ADD COLUMN delete_ind integer

CREATE OR REPLACE FUNCTION trigger_test_1_soft_delete()  
  RETURNS trigger AS $$
    DECLARE
      command text := ' SET delete_ind = 1 WHERE uuid_col = $1';
    BEGIN
      EXECUTE 'UPDATE ' || "LIBN02"."trigger_test_1" || command USING OLD.uuid_col;
      RETURN NULL;
    END;
  $$ LANGUAGE plpgsql;

CREATE TRIGGER test_1_soft_delete_trigger  
  BEFORE DELETE ON "LIBN02"."trigger_test_1"
  FOR EACH ROW EXECUTE PROCEDURE trigger_test_1_soft_delete();

After all of the above is done, I run the following delete statement:

DELETE FROM "LIBN02"."trigger_test_1"

I get the following error:

ERROR:  missing FROM-clause entry for table "LIBN02"
LINE 1: SELECT 'UPDATE ' || "LIBN02"."trigger_test_1" || command
                            ^
QUERY:  SELECT 'UPDATE ' || "LIBN02"."trigger_test_1" || command
CONTEXT:  PL/pgSQL function trigger_test_1_soft_delete() line 5 at EXECUTE

********** Error **********

ERROR: missing FROM-clause entry for table "LIBN02"
SQL state: 42P01
Context: PL/pgSQL function trigger_test_1_soft_delete() line 5 at EXECUTE

What should I change in order for this to work?

Simply use `UPDATE "LIBN02".trigger_test_1 set SET delete_ind = 1 WHERE uuid_col = OLD.uuid_col;` — , Jan 19 '17 at 22:17
Please read this question: [Are soft deletes a good idea? (use simple archive table and move the rows there instead)](http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea) — krokodilko, Jan 20 '17 at 04:02

score 3 · Accepted Answer · answered Jan 20 '17 at 09:36

The error you are receiving is because "LIBN02"."trigger_test_1" is not a string (these are quoted with single quotes), but an identifier. You should use

EXECUTE 'UPDATE "' || TG_TABLE_SCHEMA || '"."' || TG_TABLE_NAME || '" ' || command
   USING OLD.uuid_col;

You could also add AND NOT deleted to the WHERE clause to avoid unnecessary churn.

how to properly implement soft delete in postgresql?

1 Answers1