PostgreSQL: Using subquery abbreviation ('AS') in the WHERE clause

Question

Hi,

Consider the following query in PostgreSQL:

SELECT 
    a, b, 
   (A VERY LONG AND COMPLICATED SUBQUERY) AS c,
   (ANOTHER VERY LONG AND COMPLICATED SUBQUERY) AS d
FROM table

I want to have c and d in the WHERE clause, like:

WHERE c AND d;

But, as far as I know, I can only do:

WHERE A VERY LONG AND COMPLICATED SUBQUERY) AND 
   (ANOTHER VERY LONG AND COMPLICATED SUBQUERY)

Which is clumsy, code-replicating, breaking the single-choice principle and utterly ugly.

By the way, the same problem applies to the SELECT clause: I can not use abbreviations for previously-defined subqueries.

Answer 1

You could use a subquery:

SELECT a,b,c,d FROM
    (SELECT 
        a, b, 
        (A VERY LONG AND COMPLICATED SUBQUERY) AS c,
        (ANOTHER VERY LONG AND COMPLICATED SUBQUERY) AS d
        FROM table
    ) AS T1
WHERE c AND d

You could also do this with a CTE.