How to avoid "blank" insert when inserting data into a view with a trigger & insert procedure?

Question

I'm trying to update tables from insert or update call on a PostgreSQL view. Here's a simplified example of what I do:

[Person] table:
id | lastname | firstname | city | age

[Person_View] table:
id | lastname | firstname | city

Here is the trigger and the related procedure :

CREATE TRIGGER tg_update_person_view
    INSTEAD OF INSERT OR UPDATE OR DELETE ON
       Person_View FOR EACH ROW EXECUTE PROCEDURE update_person_view_table();

CREATE OR REPLACE FUNCTION update_person_view_table()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
   BEGIN
      IF TG_OP = 'INSERT' THEN
       INSERT INTO Person (id, lastname, firstname)
        VALUES(NEW.id, NEW.lastname, NEW.firstname);
       RETURN NEW;
      ELSIF TG_OP = 'UPDATE' THEN
       UPDATE Person
        SET id=NEW.id, lastname=NEW.lastname, firstname=NEW.firstname
        WHERE id=OLD.id;
       RETURN NEW;
      END IF;
      RETURN NEW;
    END;
$function$;

If I do:

INSERT INTO Person_View (id, city) VALUES ('3', 'Berlin')

A row with only the ID is added to the view and the parent table.

How can I check in the procedure that columns in which values are being inserted have a "mapping" defined in the procedure and if there ain't any mapped columns, it does not proceed ?

In Postgres 9.3+ you do not need rules nor triggers, a view based on a single table is automatically updateable. — klin, Dec 13 '16 at 20:06
I know, I should have said that this simplified example is automatically updatable. My real situation is that I have views based on multiple tables and I don't want these empty records to occur. — kaycee, Dec 13 '16 at 20:18

klin · Answer 1 · 2016-12-14T15:51:14.937

You can define a check constraint on the table, e.g.:

create table person(
    id int primary key, 
    lastname text, 
    firstname text, 
    city text, 
    age int,
    check(coalesce(lastname, firstname, city, age::text) is not null)
);

insert into person (id)
values (1);

ERROR:  new row for relation "person" violates check constraint "person_check"
DETAIL:  Failing row contains (1, null, null, null, null).

The solution works regardless whether any views based on the table were created or not.

Erwin Brandstetter · Answer 2 · 2021-12-04T16:14:54.500

Have a separate trigger & trigger function for ON DELETE to simplify. (You are not doing anything ON DELETE anyway?)

A CHECK constraint like klin suggested seems like a good idea. You don't need COALESCE and casting, though. Check a row value for NULL.

CHECK (NOT ROW(lastname, firstname) IS NULL)  -- ROW keyword is noise

This enforces at least one notnull value in the row. Works for any number of columns and any data type.

Note in particular that ROW(lastname, firstname) IS NOT NULL is not the same and would not work. Detailed explanation:

NOT NULL constraint over a set of columns

If the CHECK constraint is not an option, you can use the same expression in a trigger - which should be faster than adding it to the trigger function. The manual on CREATE TRIGGER:

Also, a trigger definition can specify a Boolean WHEN condition, which will be tested to see whether the trigger should be fired. In row-level triggers the WHEN condition can examine the old and/or new values of columns of the row.

CREATE TRIGGER tg_update_person_view
INSTEAD OF INSERT OR UPDATE ON Person_View
FOR EACH ROW 
WHEN (NOT (NEW.lastname, NEW.firstname) IS NULL)  -- more columns?
EXECUTE PROCEDURE update_person_view_table();

If the WHEN expression does not evaluate to TRUE, the trigger function is not even called - so it does not proceed like requested.

However, I missed your trigger INSTEAD OF. The manual:

INSTEAD OF triggers do not support WHEN conditions.

In this case you have to move the check into the function body:

IF NOT (NEW.lastname, NEW.firstname) IS NULL THEN
  --  do stuff
END IF;

I get this error: `ERROR: INSTEAD OF triggers cannot have WHEN conditions ********** Error **********`. — kaycee, Dec 14 '16 at 14:14
@kaycee: Oh, sorry. Not for `INSTEAD` triggers. You have to move the check into the function in this case. (Did you consider the `CHECK` constraint?) — Erwin Brandstetter, Dec 14 '16 at 14:24

How to avoid "blank" insert when inserting data into a view with a trigger & insert procedure?

2 Answers2