Use Postgresql function in SELECT and WHERE

Question

Supposing there is query, which calculates distance between given point and points stored in database. The purpose is to find few closest points to a given one

select x,y, get_distance(25,13,x,y) as dis
from points
order by dis

But as a condition, i'd like to set restriction for distance in order to get only results with distance <1000

select x,y, get_distance(25,13,x,y) as dis
from points
where dis <1000
order by dis

result of this query is : ERROR: column "dis" does not exist

So i'm forced to write function call twice:

  select x,y, get_distance(25,13,x,y) as dis
    from points
    where get_distance(25,13,x,y) <1000
    order by dis

Althought, function get_distance is declared as immutable and does not compute twice as declared in docs, the query itself looks ugly.

Is there any way to use result of get_distance as dis in condition and not to write it twice?

Paul Draper · Accepted Answer · 2014-01-22 06:57:21Z

1

Yes.

SELECT * FROM (
    SELECT x, y, get_distance(25, 13, x, y) dis FROM points
) t
WHERE dis < 1000
ORDER BY dis

edited Jan 22, 2014 at 6:57

answered Jan 22, 2014 at 6:08

Paul Draper

84.2k53 gold badges216 silver badges303 bronze badges

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

4 Comments

Alex Siri Over a year ago

It is possible that this version will have to create a full temp table, with all the distances first, and then discard and order the points. The other version, although uglier to read, would discard points as soon as they did not match, and then create the temp table for ordering. So, depending on how many points there are and how many match the condition, this version could be very slow.

Paul Draper Over a year ago

@AlexSiri, you have only a guess. And I suspect it's wrong. The SQL describes the data I want to get. The database engine is welcome to filter, sort, etc. in whatever order it wants. There is no imperative programming here. This may do what you suggest. But without evidence, I'd lean towards the assumption that PostgreSQL is smart.

Alex Siri Over a year ago

you might be right, as I created this fiddle (sqlfiddle.com/#!15/d0a9f/3) and found that both options have the same execution plan (at least with very little data)

xardas Over a year ago

As a remark, just tested both queries with explain (analyze ,buffers) and the was no difference in query plan at all.

Collectives™ on Stack Overflow

Use Postgresql function in SELECT and WHERE

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related