Função PostgreSQL não executada quando chamada de dentro do CTE

Esse é o tipo de comportamento esperado. CTEs são materializados, mas há uma exceção.

Se um CTE não for referenciado na consulta pai, ele não será materializado. Você pode tentar isso, por exemplo, e funcionará bem:

WITH not_executed AS (SELECT 1/0),
     executed AS (SELECT 1)
SELECT * FROM executed ;

Código copiado de um comentário na postagem do blog de Craig Ringer:
CTEs do PostgreSQL são cercas de otimização .

Antes de tentar esta e várias consultas semelhantes, pensei que a exceção era: "quando um CTE não é referenciado na consulta pai ou em outro CTE e não se referencia a outro CTE". Então, se você queria que o CTE fosse executado, mas os resultados não aparecem no resultado da consulta, pensei que isso seria uma solução alternativa (referenciando-o em outro CTE).

Mas, infelizmente, não funciona como eu esperava:

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

e, portanto, minha "regra de exceção" não está correta. Quando uma CTE é referenciada por outra CTE e nenhuma delas é referenciada pela consulta pai, a situação fica mais complicada e não sei exatamente o que acontece e quando as CTEs são materializadas. Também não consigo encontrar nenhuma referência para esses casos na documentação.

Não vejo solução melhor do que usar o que você já sugeriu:

SELECT * FROM __post_users_id_coin(10, 1) ;

ou:

WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1))
SELECT *
FROM test ;

Se a função atualizar várias linhas e você obtiver muitas linhas (com 1) no resultado, poderá agregar para obter uma única linha:

SELECT MAX(1) AS result FROM __post_users_id_coin(10, 1) ;

mas eu preferiria ter o resultado da função que faz uma atualização retornado, SELECT *como seu exemplo, então o que quer que chame esta consulta saiba se houve atualizações e quais foram as alterações na tabela.

Esse é um comportamento esperado e documentado.

Tom Lane explica aqui.

Documentado no manual aqui:

As instruções de modificação de dadosWITH são executadas exatamente uma vez e sempre até a conclusão , independentemente de a consulta primária ler todas (ou mesmo algumas) de suas saídas. Observe que isso é diferente da regra para SELECTin WITH: conforme declarado na seção anterior, a execução de a SELECTé realizada apenas até onde a consulta primária exigir sua saída .

Ênfase em negrito minha. "Modificação de dados" são INSERT, UPDATEe DELETEconsultas. (Em oposição a SELECT.). O manual mais uma vez:

Você pode usar instruções de modificação de dados ( INSERT, UPDATEou DELETE) em WITH.

função adequada

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;

Abandonei as cláusulas padrão (ruído) e STRICTé o sinônimo curto paraRETURNS NULL ON NULL INPUT .

Certifique-se de alguma forma que os nomes dos parâmetros não entrem em conflito com os nomes das colunas. Prefixei com _, mas essa é apenas minha preferência pessoal.

Se coinpuder NULL, sugiro:

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

Se users.idfor a chave primária, então RETURNS TABLEnem ROWs 1000faz sentido. Apenas uma única linha pode ser atualizada/retornada. Mas isso é tudo fora do ponto principal.

chamada adequada

Não faz sentido usar a RETURNINGcláusula e retornar valores de sua função se você for ignorar os valores retornados na chamada de qualquer maneira. Também não faz sentido decompor as linhas retornadas SELECT * FROM ...se você as ignorar de qualquer maneira.

Basta retornar uma constante escalar ( RETURNING 1), definir a função como RETURNS int(ou descartar RETURNINGcompletamente e torná-la RETURNS void) e chamá-la comSELECT my_function(...)

Solução

Desde que você ...

realmente não me importo com o resultado

.. apenas SELECTuma constante do CTE. É garantido que será executado desde que seja referenciado no exterior SELECT(direta ou indiretamente).

WITH test AS (SELECT __post_users_id_coin(10, 1))
SELECT 1 FROM test;

Se você realmente tem uma função de retorno definido e ainda não se importa com a saída:

WITH test AS (SELECT * FROM __post_users_id_coin(10, 1))
SELECT 1 FROM test LIMIT 1;

Não há necessidade de retornar mais de 1 linha. A função ainda é chamada.

Finalmente, não está claro por que você precisa do CTE para começar. Provavelmente apenas uma prova de conceito.

Intimamente relacionado:

• PostgreSQL parece ignorar RAISE EXCEPTION em um CTE

Resposta relacionada no SO:

• PostgreSQL: usando chaves estrangeiras, exclua o pai se não for referenciado por nenhum outro filho

E considere:

• Vários CTE em uma única consulta