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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
