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

[BladeOfLight16 <bladeoflight16@xxxxxxxxx>]

On Tue, Oct 22, 2013 at 3:15 PM, Moshe Jacobson <moshe@xxxxxxxxxxxx> 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.