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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
