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

[Moshe Jacobson <moshe@xxxxxxxxxxxx>]

On Mon, Oct 21, 2013 at 7:52 PM, David Johnston <polobo@xxxxxxxxx> wrote:

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).

Here is the full code. It is not “minimal”, but actually what we are using.
fn_get_create_or_update_space_sku() will create a non-existent row, or update it with the passed-in data if it already exists.
You’ll notice that in this version I don’t use NOT IN( ) but rather another CTE with a left join.
It behaves the same way.
I’ve put $varname in certain places to indicate that a value is going to go in there. Some of these are actually bound with placeholders, but I left it like this for clarity.

with tt_space_sku_data as
(
  select unnest(array[$sku_array]) as sku,
         unnest(array[$quantity_array]) as quantity , 
         unnest(array[$primary_array])  as primary ,
         unnest(array[$position_array]) as position
),
tt_space_skus as
(
   select fn_get_create_or_update_space_sku
       (
           $pk_space ,
            tt.sku ,
           tt.quantity ,
           tt.primary ,
           tt.position ,
           TRUE 
       ) as space_sku
   from tt_space_sku_data tt
),
tt_space_skus_to_delete as
 (
   select ss.space_sku
     from tb_space_sku ss
   left join tt_space_skus tt
       on tt.space_sku = ss.space_sku 
     where tt.space_sku is null
       and ss.space = $pk_space
)
delete from tb_space_sku ss
  using tt_space_skus_to_delete tt
 where ss.space = $pk_space
   and ss.space_sku = tt.space_sku

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc.
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

“Quality is not an act, it is a habit.” — Aristotle