列不存在(在查询中创建列)

Question

运行此查询时，将在SELECT部分的postgres中获取错误‘列’“error”不存在“。“距离”是指给定坐标的两个点之间的距离。我想这是时间问题吧？函数在发生错误之前被正确创建。

CREATE OR REPLACE FUNCTION pg_temp.earthDistance(lat1 double precision, lng1 double precision, lat2 double precision, lng2 double precision)
  RETURNS double precision AS
$BODY$
SELECT 
  asin(
  sqrt(
    sin(radians($3-$1)/2)^2 +
    sin(radians($4-$2)/2)^2 *
    cos(radians($1)) *
    cos(radians($3))
  )
  ) * 7918 AS distance;
$BODY$
  LANGUAGE sql IMMUTABLE;

SELECT populated_place.name AS populated_place_name, 
  feature.name AS feature_name, 
  ROUND(
    pg_temp.earthDistance(
      populated_place.latitude,
      populated_place.longitude,
      feature.latitude,
      feature.longitude)::NUMERIC,
  2) AS distance,
  RANK() OVER (PARTITION BY populated_place_name ORDER BY distance) AS rank
FROM populated_place JOIN feature ON
  feature.type='summit' AND
  populated_place.population>=100000
WHERE distance<=200
ORDER BY populated_place_name, rank;

Answer 1

不能在distance子句中使用别名( WHERE )。使用派生表：

SELECT 
    *, 
    RANK() OVER (PARTITION BY populated_place_name ORDER BY distance) AS rank
FROM (  
    SELECT 
        populated_place.name AS populated_place_name, 
        feature.name AS feature_name, 
        ROUND(
            pg_temp.earthDistance(
            populated_place.latitude,
            populated_place.longitude,
            feature.latitude,
            feature.longitude)::NUMERIC, 2) AS distance
    FROM populated_place 
    JOIN feature ON
        feature.type='summit' AND
        populated_place.population>=100000
    ) sub
WHERE distance <= 200
ORDER BY populated_place_name, rank;

另见：Using an Alias in a WHERE clause。