upsert with trigger (or rule)

             Hello,

I’m trying to rewrite inserts to upserts on a table when a certain
column has a certain value. Reason: the inserts are coming from an ORM.
It’s possible to send upsert from the ORM, however, in this case I find
it more elegant and future-proof to deal with it at the DB level.

First attempt:

create rule messages_insert_draft as on insert to messages where
new.is_draft do instead insert into messages values (new.*) on conflict
(sender_id, recipient_id) where is_draft do update set body =
excluded.body, updated_at = excluded.updated_at;

This has two disadvantages:

1. It doesnt work because of the endless recursion. Is there a way to
deal with the recursion without adding another column to the table?
2. Every time the table’s sctructure changes the rule has to be updated too.


With trigger:

create function trigger_messages_insert_draft() returns trigger as $$

begin

   insert into messages values (new.*) on conflict (sender_id,
recipient_id) where is_draft do update set body = excluded.body,
updated_at = excluded.updated_at;

   return null;

end;

$$ language plpgsql;


create trigger messages_before_insert_draft before insert on messages
for each row when ( new.is_draft and pg_trigger_depth() = 0 ) execute
procedure trigger_messages_insert_draft();

This works fine. As far as I see adding new columns to messages table
won’t require updating the procedure. Which is great.

There is one issue though. The orm sends 'insert into messages ....
returning id'. As the original insert is skipped, the id, which is a
serial, is not returned, so the orm can’t see the new/updated record's id.

Is there a way to make the 'returning id' part work?

         M.