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

On Tue, Oct 22, 2013 at 3:15 PM, Moshe Jacobson <moshe(at)neadwerx(dot)com> wrote:
>
> 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
>
>
>
Oops. Messed up and didn't include the PG user's list on the recipients the
first time.

Original message:

I must say this is quite difficult to interpret, which in and of itself is
a reason to rewrite it.

First, instead of having 1 array per column, pass in a single set of rows
instead (could still be an array). If you can't pass your data to the
database in that form, consider having a separate function that turns your
multiple arrays into a set of rows and pass the result of that function
into this one.

I've created a SQL Fiddle that implements UPSERT on an example table:
http://sqlfiddle.com/#!12/4b716/1/0. Look over in the schema definition for
the function and where the function is called. I'm sure you could do better
than the very ugly SELECT query I have to call the function, but if you
can't find a better way, at least it works. The basic idea is to have a
function that takes a set of rows for the table, UPDATE the rows that are
already there, and then INSERT the rows that are not. Straightforward and
to the point. I'd appreciate any ideas from veterans. =) I believe my
function requires only 2 SELECTs on the table itself, which I believe is
the same number required for your definition above.

This doesn't depend on CTE behavior, and I find it simpler and easier to
interpret (and therefore more maintainable). Does that suit your needs?

New Info:

I've improved that SQL query a bit: http://sqlfiddle.com/#!12/11ebc/1/0

Also, I forgot to mention that you'll need to remove the forward slashes.
They're an artifact of using SQL Fiddle. It was trying to split my CRETE
FUNCTION statement on the semicolon inside the definition string.

Hope this helps.