I have an
events table which is a denormalized table containing
item data. There is a one-to-many relationship between items and events.
CREATE TABLE events
id UUID PRIMARY KEY,
item VARCHAR(16) NOT NULL,
created TIMESTAMPTZ NOT NULL...
I also have a
SELECT DISTINCT view called
items which essentially takes the last event row for each
item. I would really like to be able to insert into that view as though it were a physical table, including a constraint error if the user-provided
item ID already exists.
I believe I need to create a trigger function, but I'm not sure what the query would look like to return a constraint violation (or maybe some other exception that callers can catch?). I think trigger functions have to be PL/pgSQL, but ideally it would hew as closely as possible to plain SQL.