How can I use a function's output in HAVING clause?

Question

While converting from MySQL 5.5 to PostgreSQL 9.4, I've had problems with this query:

SELECT *, GCDist(?, ?, lat, lon) AS dist 
FROM ads 
HAVING dist < radius 
ORDER BY date_created DESC 
LIMIT ?;

where GCDist computes the great-circle distance between two points.

Without the HAVING clause, the query works fine on the Postgres, but if I want to filter out the rows with dist > radius I receive this error:

ERROR: column "dist" does not exist
LINE 1: ...*, GCDist(0, 0, lat, lon) AS dist FROM ads HAVING dist < 100...

Is it possible to use the output of a function in the HAVING clause of a query in PostgresSQL 9.4? If so, how?

Thank you in advance for any hint.

Here's how to reproduce the error:

CREATE FUNCTION GCDist (
        _lat1 FLOAT,  -- Scaled Degrees north for one point
        _lon1 FLOAT,  -- Scaled Degrees west for one point
        _lat2 FLOAT,  -- other point
        _lon2 FLOAT
    ) RETURNS FLOAT
    IMMUTABLE AS
$$
    -- Hardcoded constant:
    DECLARE
        _deg2km FLOAT DEFAULT 0.0111325;
        _deg2rad FLOAT DEFAULT PI()/1800000;  -- For scaled by 1e4 to MEDIUMINT
        _rlat1 FLOAT DEFAULT _deg2rad * _lat1;
        _rlat2 FLOAT DEFAULT _deg2rad * _lat2;
    -- compute as if earth's radius = 1.0
        _rlond FLOAT DEFAULT _deg2rad * (_lon1 - _lon2);
        _m     FLOAT DEFAULT COS(_rlat2);
        _x     FLOAT DEFAULT COS(_rlat1) - _m * COS(_rlond);
        _y     FLOAT DEFAULT               _m * SIN(_rlond);
        _z     FLOAT DEFAULT SIN(_rlat1) - SIN(_rlat2);
        _n     FLOAT DEFAULT SQRT(_x * _x + _y * _y + _z * _z);
    BEGIN
        RETURN _deg2km * 2 * ASIN(_n / 2) / _deg2rad;   -- again--scaled degrees
    END;
$$
LANGUAGE plpgsql;

CREATE TABLE test (id SERIAL PRIMARY KEY, lat INTEGER NOT NULL, lon INTEGER NOT NULL);

INSERT INTO test (id, lat, lon) VALUES (DEFAULT, 10000, 10000);
INSERT INTO test (id, lat, lon) VALUES (DEFAULT, 20000, 20000);
INSERT INTO test (id, lat, lon) VALUES (DEFAULT, 50000, 50000);

SELECT *, GCDist(0, 0, lat, lon) AS dist FROM test HAVING dist < 200;

The output table on MySQL would be similar to the following:

id | lat | lon | dist
---+-----+-----+------
1  |10000|10000|157.43

score 3 · Accepted Answer · edited May 23 '17 at 11:58

3

The having is useless (and wrong) in the first place because you are not using group by, you should use a where clause in this case.

To access a column alias in the where clause you need to wrap the query in a derived table:

select *
from (
  SELECT *, 
         GCDist(0, 0, lat, lon) AS dist 
   FROM test 
) t 
where dist < 200;

Or just repeat the expression

SELECT *, GCDist(0, 0, lat, lon) AS dist 
FROM test 
WHERE GCDist(0, 0, lat, lon) < 200;

See also here:

edited May 23 '17 at 11:58

Community

1
1

answered Jul 10 '15 at 19:38

Well, from what I understood, in MySQL the difference is in the order of execution (WHERE selects before the function's execution, while HAVING selects after), so HAVING was the only way. About your first suggestion, aren't subqueries "expensive"? I mean, why should the DBMS create another table just for that calculation, when it could simply append those values? (I don't know how the query is optimised, but based on syntax, it seems slower) About your second suggestion, will `GCDist` execute twice? Because if it so, I can't use it. That function is already slow as it is. – hornobster Jul 10 '15 at 19:52
@CarloVespa, have you tried a_horse_with_no_name's solution? Check the execution times. You'd be surprised because optimizers are better than you think. – Emacs User Jul 10 '15 at 20:16
@EmacsUser Yes, I'm currently using his first solution. I know optimisers are great, but it just seems so awkward to me that you need two `SELECT`s for such a simple thing. – hornobster Jul 10 '15 at 20:20
1

@CarloVespa: there is no sub-query in my solution. It's a **derived table** and it's merely syntactic sugar. There is no performance overhead by using it. – Jul 10 '15 at 20:30
Thanks for the clarification. – hornobster Jul 10 '15 at 20:42

score 1 · Answer 2 · answered Jul 10 '15 at 20:21

1

Is it possible to use the output of a function in the HAVING clause of a query in PostgresSQL 9.4? If so, how?

Yes, and same as in WHERE clause. See the second of the two solutions shown by a-horse-with-no-name.

answered Jul 10 '15 at 20:21

Emacs User

1,457
1
12
19

How can I use a function's output in HAVING clause?

2 Answers2