Re: Missing feature - how to differentiate insert/update in plpgsql function?

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.

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

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