I am trying to use result of function execution in where
clause but with no success:
SELECT clinics.*, distance_between_objects(1, id, 7, 3) AS dist FROM clinics WHERE dist<=1;
gives me:Column "dist" does not exists
.
Quoting it like:
SELECT clinics.*, distance_between_objects(1, id, 7, 3) AS dist FROM clinics WHERE "dist"<=1;
doesn't helps either. Please advise is there possibility in Postgres to use function result in WHERE clause without calling it twice? Thanks!
To avoid calling distance_between_objects
twice:
--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
is a cleaner approach in my opinion.