Re: [GENERAL] Missing feature - how to differentiate insert/update inplpgsql function?

On 02/15/2017 06:27 AM, hubert depesz lubaczewski wrote:
> On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote:
>> On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote:
>>> Hi,
>>> I have a function, in PostgreSQL 9.6, which does:
>>>
>>> INSERT INTO table () values (...)
>>> ON CONFLICT DO UPDATE ...;
>>>
>>> The thing is that the function should return information whether the row
>>> was modified, or created - and currently it seems that this is not
>>> available. Or am I missing something?
>>
>> All I can think of is to use:
>>
>> RETURNING pk
>>
>> and see if that changed or not.
>
> Well, this wouldn't work for me as pkey will not change.

Alright you lost me. If the pkey does not change then how do you get new
rows(INSERT)?

>
> For my particular case, I have this table
> create table t (
>     a_from text,
>     a_to text,
>     created timestamptz,
>     updated timestamptz,
>     primary key (a_from, a_to)
> );
>
> where created and updated are set (and kept correct) with triggers. And
> in my insert/update, if the row exists, I just set updated to now().

This I understand, though it does not square with the above.

>
> So, for my particular case, I can, and do, compare if created is the
> same as updated, and if no - it was update, otherwise - insert.
>
> But it would be really good to get some proper support for
> differentiating flow of such queries...
>
> depesz
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com