Re: BUG #14609: ON CONSTRAINT (aka UPSERT) code fails when excluded.<columname> used in calculation.

Typo in last post:

Before: It is as if I can’t reference “excluded,sendingfacility” in the calculation.
After: It is as if I can’t reference “excluded.sendingfacility” in the calculation.

I didn't answer part of your question: "... a hard time following the expression in the UPDATE part of your INSERT within the plpgsql function. What is this supposed to do?"

Part one (the calculation):

Essentially:

IF value ='""', set the column to NULL. Those are two double quotes wrapped in single quotes.
If value = '', persist the column value that is already in the row. That is an empty string, i.e. quote quote.
If value = 'ASDF', set the column value to 'ASDF'

This is a concept used in HL7 whereby messages are sent over and over for a patient and the receiver is expected to "upsert" data. HL7 has no concept of NULL so double quotes are used to say "wipe out what you have".

This inline code demonstrates the behavior of the calculations, just modify p_sendingfacilty and execute.

DO $$
DECLARE
sendingfacility varchar:= 'Duke';
p_sendingfacility varchar:='ASDF';
result varchar:= NULLIF(COALESCE(NULLIF(p_sendingfacility,''),sendingfacility),'""');
BEGIN
RAISE NOTICE '%',result;
END
$$;

Part two (why the WHERE clause).

WHERE excluded.mrn = p_mrn and excluded.site_code = p_site_code

Likely my unfamiliarly with the fact the INSERT... ON CONFLICT ON CONSTRAINT already knows the row to which apply the update.

-----Original Message-----
From: Paul Coyne
Sent: Friday, March 31, 2017 5:47 PM
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: PostgreSQL mailing lists <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: [BUGS] BUG #14609: ON CONSTRAINT (aka UPSERT) code fails when excluded.<columname> used in calculation.

This does not work (all other code the same for the SP but omitted for brevity):

SET sendingfacility = NULLIF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')

This does work as intended.

SET sendingfacility = NULLIF(COALESCE(NULLIF(p_sendingfacility,''),test.sendingfacility),'""')

It is as if I can’t reference “excluded,sendingfacility” in the calculation.

The is irrespective of the WHERE clause, but I can remove it as you mention.

On 3/31/17, 4:54 PM, "Peter Geoghegan" <pg(at)bowt(dot)ie> wrote:

On Fri, Mar 31, 2017 at 5:10 PM, <paul(dot)coyne(at)diridium(dot)com> wrote:
> Can excluded.variablename be used in a calculation? It appears to have the
> value NULL in the formula.

I'm on a flight, and haven't looked at this properly, but I have a
hard time following the expression in the UPDATE part of your INSERT
within the plpgsql function. What is this supposed to do?

IF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')
WHERE excluded.mrn = p_mrn and excluded.site_code = p_site_code

It's also weird that "WHERE excluded.mrn = p_mrn" is there, since
you're proposing that same value (function argument) for insertion in
the first place (same with site_code).

Are you sure that this isn't just an "IS NULL vs. =" issue?

--
Peter Geoghegan