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