Ignore column on PostgreSQL view insert

Question

A library that I use currently has a bug and I'm trying to find a workaround for. Is it possible to to ignore a column on a PostgreSQL view insert? I have this view:

CREATE VIEW schema.auth AS
  SELECT email AS id, pass AS pass, 'onymous'::varchar AS rolname
    FROM schema.person;

The library is trying to do the following:

INSERT INTO schema.auth (id, pass, rolname) VALUES ('abc', '123', '');

The library should not be setting an empty string ('') for rolname, hence the bug.

Is there a way for me to stop PostgreSQL from throwing an error at rolname and quietly discard the empty string?

possible duplicate of Postgresql insert trigger to set value — Bulat
– Bulat, Commented Sep 19, 2015 at 22:14
It is my understanding that triggers don't work that way on views. — Calebmer
– Calebmer, Commented Sep 19, 2015 at 22:15
Yes. I tried adding a trigger before inserts/updates. Even then though, I'm still not sure how to remove a column. — Calebmer
– Calebmer, Commented Sep 19, 2015 at 22:20
You can replace the empty string with a value that will not cause the bug, e.g. NULL — Bulat
– Bulat, Commented Sep 19, 2015 at 22:31

Patrick · Accepted Answer · 2015-09-20 04:30:28Z

1

This is actually very trivial. What you do is simply "forget" rolname when propagating the INSERT on the view to the underlying tables:

CREATE FUNCTION fix_lib_issue() RETURNS trigger AS $$
BEGIN
  INSERT INTO schema.person (email, pass)
  VALUES NEW.id, NEW.pass;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_auth
INSTEAD OF INSERT ON schema.auth
FOR EACH ROW EXECUTE PROCEDURE fix_lib_issue();

When you query the view after the insert, the rolname will be set to onymous, following the view definition.

answered Sep 20, 2015 at 4:30

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ignore column on PostgreSQL view insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related