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

On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote:
> 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)
> >);

Well, if I do:

insert into t (a_from, a_to)

and will use some values that do not exist in table, then insert
happens, but not sure what do you mean about "primary key change" in
this case.

On the other hand, if the from/to already exists in the table, then
update happens (on "updated" column) - and then there is definitely no
pkey change.

Best regards,

depesz