Default values in Postgres view with triggered instead-of update

Question

I was planning on having a view with instead of insert trigger. There seems to be a problem with inserting a default value though.

Having the trigger set up as below, the following query fails

INSERT INTO v1.clients (foo) VALUES ('bar')

this returns a

null value in column "is_admin" violates not-null constraint

even though the underlying data.users (not null) table has a default value set.

What I'd say happens is the view translates all missing values to null and the instead of is applied, trying to insert null to a not null default false column.

Can I somehow set up the trigger to instead of trying to insert null to insert the default value? Having coalesce(NEW.is_admin, default) in the relevant insert in the trigger is a syntax error. I would rather not duplicate the default value manually in trigger.

Is this supported in postgres? What would be the best approach to split a view of two tables to those tables, while allowing default values?

definitions:

CREATE OR REPLACE VIEW v1.clients AS
    SELECT
        c.id, c.foo,
        u.id user_id, u.is_admin
    FROM data.clients c
    INNER JOIN data.users u ON u.client_id = c.id;

CREATE FUNCTION data.separate_client_user_data()
RETURNS TRIGGER AS $$
DECLARE
    client_id clients.id%TYPE;
BEGIN
    INSERT INTO data.clients (foo) VALUES (NEW.foo) RETURNING id INTO client_id;
    INSERT INTO data.users (client_id, is_admin)
        VALUES (client_id, NEW.is_admin);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER user_data_trigger
INSTEAD OF INSERT ON v1.clients
FOR EACH ROW EXECUTE PROCEDURE data.separate_client_user_data();

murftown · Accepted Answer · 2017-12-30 17:39:30Z

Late to the game, I know, but I have extra insight I want to share.

Note that, as Patrick said, the NEW variable that the TRIGGER PROCEDURE uses contains all the fields - with NULLs added for any fields that were not originally part of your INSERT statement.

As you pointed out, the underlying table has a default value for the is_admin field, and unfortunately that default value doesn't get a chance to be seen, because the NEW variable already has NEW.is_admin = NULL.

However, I want to point out a different solution from Patrick's. It's true that you can hand-code the IF checks (essentially re-implementing the default value logic!) in your plpgsql function. But this may feel non-optimal from a DRY perspective. Here was the key for me when I had your problem:

TL/DR

The default values of the view itself are honored when building the NEW variable. Therefore if you do ALTER TABLE <view_name> ALTER is_admin SET DEFAULT false it should cause false to be passed in to NEW.is_admin, instead of NULL.

Hope that helps!

Patrick · Accepted Answer · 2015-07-26 07:34:33Z

In a trigger function, the NEW and OLD implicit parameters always contain all the fields of the underlying table or view, with NULL assigned to fields for which no data is available. This is the correct behaviour, otherwise you could never assign a value to a field with no data.

In case you have a field with a NULL value and you want to get the DEFAULT value upon INSERT instead, you should test for it prior to doing the INSERT:

CREATE FUNCTION data.separate_client_user_data() RETURNS trigger AS $$
DECLARE
    cid clients.id%TYPE; -- Don't use variable with same name as a column
BEGIN
    INSERT INTO data.clients (foo) VALUES (NEW.foo) RETURNING id INTO cid;
    IF NEW.is_admin IS NULL THEN
        INSERT INTO data.users (client_id)
            VALUES (cid);   -- Use default value for is_admin
    ELSE    
        INSERT INTO data.users (client_id, is_admin)
            VALUES (cid, NEW.is_admin);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Collectives™ on Stack Overflow

Default values in Postgres view with triggered instead-of update

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related