postgresqlselectwhere-clausesql-function

Using function result in WHERE clause in PostgreSQL


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!


Solution

  • 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.