On 19/07/16 23:45, Adrian Klaver wrote:
>
> To be more complete it would nice to see the schema definition for the
> table messages.
>
> Also maybe some idea of what you the code is supposed to do. If I
> understand it correctly:
>
> 1) Check if a message is a draft.
>
> 2) Check if there is a uniqueness conflict with an existing
> (sender_id, recipient_id) combination
>
> 3) If 1) and 2) are true then UPDATE the fields body and updated_at of
> the existing record with the NEW.body and NEW.updated_at data.
The table and the triggers altogether are huge.
Anyway, you got it right, the point is that the recipient_id and
sender_id must be unique only if the message is a draft (autosave feature).
But I’m looking for a general solution as there are other tables and
other cases when "converting" the insert to upsert would be awesome.
Let’s say that a part of the system will be accessed by another ORM to
increase the performance. (Hello Rust). Only a DB-level solution
requires no code duplication.
Accessing the DB with multiple ORMs isn’t uncommon on large scale so
this might be not only my issue.
> This alone doesn’t prove that it’s not possible.
>>
>> The value returned by "returning id" might be set or read from the
>> existing or inserted record in some way.
>
> By returning NULL you said the original INSERT never happened and
> nothing is returned, so no id is returned. The embedded INSERT happens
> outside the scope of the trigger.
I see your point and you probably are right. In theory though, it’s
possible that there is a solution to manually store the returning id. I
mean maybe a system table or so. Postgresql knows what the client asked
to return. This must be somewhere. It sounds hackish though.
It hasn’t be a trigger at all. I’m 99% sure I could make it work by
using a rule and a view or parent or a child table (to avoid the endless
recursion). However, these together aren’t less complicated than doing
it through the ORM. The rule isn’t dynamic (regarding the changes in the
columns) which makes the solution problematic.
The trigger is simple. It allows adding new columns to the table without
rewriting the trigger. And it works, except that the ORM has no idea
about the new record’s id. It’s possible to find the record by the
unique colums, however, the whole point is not modifying the ORM at all.
M.