PostgreSQL function not executed when called from inside CTE

Question

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?

Answer 1

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.

Answer 2

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