CODEWITHSUNDEEP
postgresqlselectwhere-clausesql-function
Serj.by
Serj.by

Reputation: 604

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!

Upvotes: 9

Views: 9299

Answers (3)

Michel Milezzi
Michel Milezzi

Reputation: 11155

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.

Upvotes: 6

Eladio Mora
Eladio Mora

Reputation: 179

You can also use a LATERAL

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

Upvotes: 4

JGH
JGH

Reputation: 17906

You can use the function in the where clause:

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

Upvotes: -1

Related Questions