Re: Missing feature - how to differentiate insert/update in plpgsql 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(dot)klaver(at)aklaver(dot)com