CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
user7113942
user7113942

Reputation: 104

Conditionally drop an insert in a before insert trigger without returning error

I have the following function in a before insert trigger:

CREATE OR REPLACE FUNCTION schema.table_somefun()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
  IF NEW.col2 NOT NULL THEN
    NEW.col1 := CASE NEW.col1
                     WHEN '121432' THEN '321123'
                     ELSE <command> END CASE; --there should be a command aborting insertion without error or exception
  END IF;
  RETURN NEW;
END;
$BODY$

The ELSE statement should abort insertion. Is there a command which drops the query without telling it to the client and leaving the table untouched?

Upvotes: 0

Views: 124

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656481

Just use

RETURN NULL;

instead of

RETURN NEW;

to cancel the INSERT for the row and do nothing instead.

But you cannot execute a PL/pgSQL statement inside an SQL CASE expression. (Don't confuse SQL CASE with the similar control structure CASE of PL/pgSQL!)
Could look like this:

CREATE OR REPLACE FUNCTION schema.table_somefun()
  RETURNS trigger AS
$func$
BEGIN
   IF NEW.col2 NOT NULL THEN
      IF NEW.col1 = '121432' THEN  -- could also be plpgsql CASE ...
         NEW.col1 := '321123';
      ELSE
         RETURN NULL;
      END IF;
  END IF;
  RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Upvotes: 1

Related Questions