Re: upsert with trigger (or rule)

On 07/19/2016 11:56 AM, Maeldron T. wrote:
> 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();

By returning NULL in your function you are skipping the original INSERT.
I am sure exactly what you are trying to achieve, but it would seem the
thing to is check for the uniqueness of (sender_id, recipient_id) in
your function and then modify the original INSERT row as needed and then
RETURN it as NEW.

>
> 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?

Not that I know of:

https://www.postgresql.org/docs/9.5/static/plpgsql-trigger.html

"A trigger function must return either NULL or a record/row value having
exactly the structure of the table the trigger was fired for."

>
> M.
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com