Using function result in WHERE clause in PostgreSQL

Question

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!

WHERE cannot use values calculated in the same level of select because it is evaluated before you get values. On the other hand ORDER BY can because it is evaluated after all values are selected. But encapsulate function into subselect in FROM clause and it will work. — JosMac, Nov 23 '17 at 12:54

Michel Milezzi · Accepted Answer · 2020-07-03T12:10:45.250

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.

score 2 · Answer 2 · answered May 11 '20 at 00:34

2

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;

answered May 11 '20 at 00:34

Eladio Mora

159
2
6

That's a typical use-case for LATERAL, I'd go with this especially that CTEs ignore the underlying indices and make a "new" dataset with no optimisation – Rafs May 17 '22 at 12:44

score -1 · Answer 3 · answered Nov 23 '17 at 12:59

-1

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;

answered Nov 23 '17 at 12:59

JGH

15,928
4
31
48

The goal is not to call function twice. Like in michel.milezzi answer above. – Serj.by Nov 24 '17 at 13:21
1

But to not call function twice, you end by doing multiples runs (sub-queeries, sorts and use temporary space) through the database. Sometimes cleaner code is not the more efficient one database wise. Anyway as far I know, putting functions in fields on the left sode of the query is not performant unless you create a function index on it. This works for all solutions – Gilberto Pe-Curto Feb 26 '21 at 17:46

Using function result in WHERE clause in PostgreSQL

3 Answers3