Re: Bug? Function with side effects not evaluated in CTE

BladeOfLight16 wrote
> Regarding UPSERT in particular, are you working with a single row or a set
> of rows? If a single row, is there a reason you can't perform a SELECT
> before hand to see if the PK is already there and then INSERT or UPDATE
> accordingly? If multiple rows, is there a reason you can't UPDATE ...
> SELECT * FROM rows WHERE pk IN ... and then INSERT ... SELECT * FROM rows
> WHERE pk NOT IN ...? It seems to me that would be more
> readable/maintainable than relying on a particular CTE behavior.

Saw this it my inbox but not Nabble so I'll copy it here:

> From Moshe; the OP:
>
> We use stored procs to provide us the functionality of an UPSERT, which
> PostgreSQL lacks.
> We are using this in the first CTE to create new entries in a table, and
> we are using the DELETE to delete the entries that already existed that we
> didn't just create.
> I am of the belief that if the function in a CTE is volatile, that it
> should be executed unconditionally.

This seems like a terrible idea. To rephrase to make sure I understand:

You "always" insert a new record. Then, if an existing record was found you
DELETE that existing record. Thus at no point do you actually issue an
UPDATE.

How are you dealing with Primary and Foreign Keys? I guess you could defer
evaluate them so you can have a duplicate PK on the table before the
deletion occurs...though whether a FK will allow this kind of behavior I do
not know.

It would help is Moshe would post a minimally viable working example of the
entire use-case so that its desirability can be assessed and potential
short-term alternative provided since even if desired this could not be
released until 9.4 as it constitutes a behavior change (I don't think anyone
is going to accept this a being a bug-fix no matter what solution is
offered).

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Bug-Function-with-side-effects-not-evaluated-in-CTE-tp5774792p5775359.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.