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

On Mon, Oct 21, 2013 at 7:52 PM, David Johnston <polobo(at)yahoo(dot)com> 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