Richard Huxton <dev(at)archonet(dot)com> writes:
> SELECT
> CASE WHEN total >0 THEN total ELSE -1 END AS new_total
> FROM (
> SELECT count(*) AS total FROM test WHERE id=$1
> ) AS raw_total
Actually you could just do
SELECT
CASE WHEN count(*) >0 THEN count(*) ELSE -1 END AS total
FROM test WHERE id=$1;
PG has avoided redundant calculations of duplicate aggregates for some
time. (This doesn't help in the original formulation because it
actually had two different sub-selects; the case that is handled is
identical aggregate expressions within SELECT list or HAVING of a single
SELECT.)
regards, tom lane