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

On 02/15/2017 06:53 AM, hubert depesz lubaczewski wrote:
> 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.

Yeah I see(thanks to Karsten also). So:

CREATE TABLE upsert_test (fld_1 varchar,
fld_2 varchar,
PRIMARY KEY (fld_1,-
fld_2));

INSERT INTO upsert_test (fld_1,
fld_2)
VALUES ('test1', 'test3')
ON CONFLICT (fld_1,
fld_2)
DO
UPDATE
SET fld_1 = EXCLUDED.fld_1,
fld_2 = EXCLUDED.fld_2
RETURNING
fld_1,
fld_2;

fld_1 | fld_2
-------+-------
test1 | test3
(1 row)

INSERT 0 1

INSERT INTO upsert_test (fld_1,
fld_2)
VALUES ('test4', 'test5')
ON CONFLICT (fld_1,
fld_2)
DO
UPDATE
SET fld_1 = EXCLUDED.fld_1,
fld_2 = EXCLUDED.fld_2
RETURNING
fld_1,
fld_2;

fld_1 | fld_2
-------+-------
test4 | test5

Can see the differentiation issue now. Can't see a solution right now
other then the one you already have, a marker field that you can use to
determine INSERT/UPDATE.

>
> Best regards,
>
> depesz
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com