This is expected, documented behavior.
Tom Lane explains it here.
Documented in the manual here:
Data-modifying statements in
WITH are executed exactly once, and
always to completion, independently of whether the primary query reads
all (or indeed any) of their output. Notice that this is different
from the rule for
WITH: as stated in the previous section,
execution of a
SELECT is carried only as far as the primary query
demands its output.
Bold emphasis mine. "Data-modifying" are
DELETE queries. (As opposed to
SELECT.). The manual once more:
You can use data-modifying statements (
CREATE OR REPLACE FUNCTION public.__post_users_id_coin (_coins integer, _userid integer)
RETURNS TABLE (id integer) AS
UPDATE users u
SET coin = u.coin + _coins -- see below
WHERE u.id = _userid
$func$ LANGUAGE sql COST 100 ROWS 1000 STRICT;
I dropped default (noise) clauses and
STRICT is the short synonym for
RETURNS NULL ON NULL INPUT.
Make sure somehow that parameter names do not conflict with column names. I prepended with
_, but that's just my personal preference.
coin can be
NULL I suggest:
SET coin = CASE WHEN coin IS NULL THEN _coins ELSE coin + _coins END
users.id is the primary key, then neither
RETURNS TABLE nor
ROWs 1000 make any sense. Only a single row can be updated / returned. But that's all beside the main point.
It makes no sense to use the
RETURNING clause and return values from your function if you are going to ignore the returned values in the call anyway. It also makes no sense to decompose returned rows with
SELECT * FROM ... if you ignore them anyway.
Just return a scalar constant (
RETURNING 1), define the function as
RETURNS int (or drop
RETURNING altogether and make it
RETURNS void) and call it with
Since you ...
don't really care about the result
SELECT a constant form the CTE. It's guaranteed to be executed as long as it is referenced in the outer
SELECT (directly or indirectly).
WITH test AS (SELECT __post_users_id_coin(10, 1))
SELECT 1 FROM test;
If you actually have a set-returning function and still don't care about the output:
WITH test AS (SELECT * FROM __post_users_id_coin(10, 1))
SELECT 1 FROM test LIMIT 1;
No need to return more than 1 row. The function is still called.
Finally, it's unclear why you need the CTE to begin with. Probably just a proof of concept.
Related answer on SO: