Re: upsert with trigger (or rule)

On 07/19/2016 01:25 PM, Maeldron T. wrote:
>         Hello Adrian,
>
> On 19/07/16 21:27, Adrian Klaver wrote:
>> On 07/19/2016 11:56 AM, Maeldron T. wrote:
>>>
>>
>> By returning NULL in your function you are skipping the original INSERT.
> I know that, I wrote it.
>> I am sure exactly what you are trying to achieve,
>
> In general, there are the following conditions:
>
> The application uses ORM. The ORM generates inserts. In this case it’s
> Ruby on Rails / ActiveRecord, but it could be anything else.
>
> The business logic requires uniqueness in certain cases.
>
> Upsert is finally there in PostgreSQL.
>
> It’s possible to tell the application to send 'upsert' (on conflict do),
> however, it works by turning off the ORM and build the query. To keep
> the advantages of the ORM, one has to build a library to generate the
> queries. Actually I did it, and it works fine, but there are cases when
> overwriting the insert into upsert in the database is simpler, more
> elegant, more efficient and more stable.
>
>
>> 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.
>
> I don’t see how that would help. Except if you mean deleting the old
> record, which is neither efficient nor safe in race conditions.

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.


>
>>
>>>
>>> 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."
> 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.

>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com