Suppose I run the following query: WITH "exp" AS ( DELETE FROM "item" i USING "item_expired" e WHERE e.oref = i.ref AND i.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 NULL THEN 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql