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 '
coin = coin + coins
userid = users.id
users.id' LANGUAGE "sql" COST 100 ROWS 1000

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))

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))

test -- Select result and update performed


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?


2 Answers 2


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) ;


WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1))
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
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.

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


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))

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))

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:

Related answer on SO:

And consider:

  • Terrific, big fan and honored to have your answer too Erwin. I'm using CTEs as I'm doing an INSERT prior to the UPDATE within the same wrapping function - no transactions available.
    – Andy
    Oct 28, 2016 at 5:24
  • Nice. Just a q: is the test in WITH test AS (SELECT * FROM __post_users_id_coin(10, 1)) SELECT ... LIMIT 1; considered a modifying CTE or not? Oct 28, 2016 at 14:15
  • @ypercubeᵀᴹ: A SELECT is not "data-modifying" according to CTE terminology. I added some clarification above. It's the responsibility of the user if (s)he adds code to a function that modifies data behind the curtains. Oct 29, 2016 at 1:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.