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.


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 SELECT in 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 INSERT, UPDATE and DELETE queries. (As opposed to SELECT.). The manual once more:

You can use data-modifying statements (INSERT, UPDATE, or DELETE) in WITH.

Proper function

CREATE OR REPLACE FUNCTION public.__post_users_id_coin (_coins integer, _userid integer)
  RETURNS TABLE (id integer) AS
$func$
UPDATE users u
SET    coin = u.coin + _coins  -- see below
WHERE  u.id = _userid
RETURNING u.id
$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.

If coin can be NULL I suggest:

SET    coin = CASE WHEN coin IS NULL THEN _coins ELSE coin + _coins END

If 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.

Proper call

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 SELECT my_function(...)

Solution

Since you ...

don't really care about the result

.. just 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.

Closely related:

  • PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Related answer on SO:

  • PostgreSQL: using foreign keys, delete parent if it's not referenced by any other child

And consider:

  • Multiple CTE in single query

Tags:

Postgresql

Cte

Recent Posts