Re: 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