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