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
– JosMac, Commented Nov 23, 2017 at 12:54

Michel Milezzi · Accepted Answer · 2020-07-03 12:10:45Z

6

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.

edited Jul 3, 2020 at 12:10

answered Nov 23, 2017 at 13:19

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eladio Mora · Accepted Answer · 2020-05-11 00:34:41Z

4

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, 2020 at 0:34

Eladio Mora

1792 silver badges6 bronze badges

1 Comment

Rafs Over a year ago

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

JGH · Accepted Answer · 2017-11-23 12:59:16Z

-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, 2017 at 12:59

JGH

18.2k5 gold badges40 silver badges57 bronze badges

2 Comments

Serj.by Over a year ago

The goal is not to call function twice. Like in michel.milezzi answer above.

Gilberto Pe-Curto Over a year ago

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

Collectives™ on Stack Overflow

Using function result in WHERE clause in PostgreSQL

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related