postgresql - 在PostgreSQL的WHERE子句中使用函数结果

我试图在where子句中使用函数执行的结果，但没有成功：

SELECT clinics.*, distance_between_objects(1, id, 7, 3) AS dist FROM clinics WHERE dist<=1;

给我：Column "dist" does not exists。
引用如下：

SELECT clinics.*, distance_between_objects(1, id, 7, 3) AS dist FROM clinics WHERE "dist"<=1;

也没用。请告知Postgres中是否有可能在WHERE子句中使用函数result而不调用它两次？
谢谢！

最佳答案

要避免调用distance_between_objects两次：

--Subquery
SELECT * FROM (
    SELECT
        *,
        distance_between_objects(1, id, 7, 3) AS dist
    FROM
        clinics) AS clinics_dist
WHERE
    dist <= 1;

--CTE
WITH clinics_dist AS (
    SELECT
        *,
        distance_between_objects(1, id, 7, 3) AS dist
    FROM
        clinics
)
SELECT
    *
FROM
    clinics_dist
WHERE
    dist <= 1;

我个人更喜欢CTE方法。