PostgreSQL function not executed when called from inside CTE 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?
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:

WITH not_executed AS (SELECT 1/0),
     executed AS (SELECT 1)
SELECT * FROM executed ;


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:

WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1)),
  execute_test AS 
    (TABLE test)
SELECT 1 ;     -- no, it doesn't do the update


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:

SELECT * FROM __post_users_id_coin(10, 1) ;


or:

WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1))
SELECT *
FROM test ;


If the function updates multiple rows and you get many rows (with 1) in the result, you could aggregate to get a single row:

SELECT MAX(1) AS result FROM __post_users_id_coin(10, 1) ;


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.