Just hoping to confirm my observation and get an explanation about why this is happening.
I have a function defined as:
CREATE OR REPLACE FUNCTION "public"."__post_users_id_coin" ("coins" integer, "userid" integer) RETURNS TABLE (id integer) AS '
UPDATE
users
SET
coin = coin + coins
WHERE
userid = users.id
RETURNING
users.id' LANGUAGE "sql" COST 100 ROWS 1000
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
When I call this function from a CTE, it executes the SQL command but does not trigger the function, for example:
WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))
SELECT
1 -- Select 1 but update not performed
On the other hand, if I call the function from a CTE and then select the result of the CTE (or call the function directly without CTE) it executes the SQL command and does trigger the function, for example:
WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))
SELECT
*
FROM
test -- Select result and update performed
or
SELECT * FROM __post_users_id_coin(10,1)
Since I don't really care about the result of the function (just need it to perform the update) is there any way of getting this to work without selecting the result of the CTE?
Best Answer
That's kind of expected behaviour. CTEs are materialized but there is an exception.
If a CTE is not referenced in the parent query then it is not materialized at all. You can try this for example and it will run fine:
Code copied from a comment in Craig Ringer's blog post:
PostgreSQL’s CTEs are optimization fences.
Before trying out this and several similar queries, I thought that the exception was: "when a CTE is not referenced in the parent query or in another CTE and doesn't reference itself another CTE". So, if you wanted the CTE to be executed but the results not shown in the query result, I thought this would be a workaround it (referencing it in another CTE).
But alas, it doesn't work as I expected:
and therefore, my "exception rule" is not correct. When a CTE is referenced by another CTE and none of them is referenced by the parent query, the situation is more complicated and I'm not sure exactly what happens and when the CTEs are materialized. I can't find any reference for such cases in the documentation either.
I don't see any better solution than using what you already suggested:
or:
If the function updates multiple rows and you get many rows (with
1
) in the result, you could aggregate to get a single row:but I'd prefer to have the results of the function that does an update returned, with
SELECT *
as your example, so whatever calls this query knows if there were updates and what the changes in the table were.