Volatile functions in WITH - Mailing list pgsql-sql

From Ben Morrow
Subject Volatile functions in WITH
Date
Msg-id 20130217075859.GE8029@anubis.morrow.me.uk
Whole thread Raw
Responses Re: Volatile functions in WITH
List pgsql-sql
Suppose I run the following query:
   WITH "exp" AS (       DELETE FROM "item" i       USING "item_expired" e       WHERE   e.oref      = i.ref
ANDi.basket    = $1       RETURNING i.basket, e.oref, e.nref, i.count, e.msg   ),   "subst" AS (       INSERT INTO
"item"("basket", "ref", "count")       SELECT e.basket, e.nref, e.count       FROM "exp" e       WHERE e.nref IS NOT
NULL  )   SELECT DISTINCT e.msg   FROM "exp" e
 

This is a very convenient and somewhat more flexible alternative to
INSERT... DELETE RETURNING (which doesn't work). However, the "item"
table has a unique constraint on (basket, ref), so sometimes I need to
update instead of insert; to handle this I have a VOLATILE function,
add_item. Unfortunately, if I call it the obvious way
   WITH "exp" AS ( -- as before   ),   "subst" AS (       SELECT add_item(e.basket, e.nref, e.count)       FROM "exp" e
     WHERE e.nref IS NOT NULL   )   SELECT DISTINCT e.msg   FROM "exp" e
 

then the planner sees that the results of "subst" are not used, and
doesn't include it in the query plan at all.

Is there any way I can tell WITH that add_item is actually a data-
modifying statement? Adding FOR UPDATE doesn't seem to help (I didn't
really expect it would.)

Alternatively, are either of these safe (that is, are they guaranteed to
call the function once for every row returned by "exp", even if the
DISTINCT ends up eliminating some of those rows)?
   WITH "exp" AS ( -- as before   ), "subst" AS ( -- SELECT add_item(...) as before   )   SELECT DISTINCT e.msg   FROM
"exp"e       LEFT JOIN "subst" s ON FALSE
 
   WITH "exp" AS ( -- as before   )   SELECT DISTINCT s.msg   FROM (       SELECT e.msg, CASE           WHEN e.nref IS
NULLTHEN NULL           ELSE add_item(e.basket, e.nref, e.count)       END "subst"   ) s
 

I don't like the second alternative much, but I could live with it if I
had to.

Ben




pgsql-sql by date:

Previous
From: Adam
Date:
Subject: Perform Function When The Rows Of A View Change
Next
From: Ben Morrow
Date:
Subject: Re: upsert doesn't seem to work..