On Mon, Oct 21, 2013 at 7:52 PM, David Johnston <polobo@yahoo.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 ttwhere 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