Steve Wampler <swampler@noao.edu> writes:
> I have a table ("attributes_table" with (say) 2 columns: name and
> value) where I want to allow an "insert_or_update" action. That is,
> if an insert comes along where the name duplicates that of an existing
> row's name field, then I want to map the insert into an update into
> attributes_table. (The table is acting as a labelled set.)
Assuming that you want *all* inserts into this table to act that way,
I'd suggest doing it with a trigger directly on that table. The trigger
would be BEFORE INSERT, and it would either allow the insert to happen
(by returning the proposed tuple) or do an UPDATE and prevent the insert
(by returning NULL). No circularity involved.
BTW, you should also have a unique index on the name column, just to
make sure that no one can put duplicate rows into the table (say, by
updating an existing row to have a different/conflicting name).
I'm not sure whether you need a view at all. Perhaps there are other
parts of your requirements that would justify a view, but not this one.
> (I deliberately disallowed deletes through the view)
If you want to disallow deletes to some users, you could do that with
GRANT/REVOKE commands. Again, that doesn't seem like a reason to have
a view.
regards, tom lane
