主页 / dba / 问题 / 57610
Accepted
oxfist
Asked: 2014-01-28 04:07:20 +0800 CST

仅当查询结果不为 NULL 时，如何正确返回查询结果？

  • 772

我正在编写一个 PL/pgSQL 函数，它为我需要检查它是否返回某些内容的查询创建一个游标。

我正在做的是这样的：

  1. 运行查询
  2. 检查它是否返回一些东西。
  3. 如果没有，请加倍参数并再次运行查询。
  4. 否则，返回查询中的所有行。

我发现检查查询是否返回带有游标的内容是最好的选择，因为它是一个非常长的查询（连接 5 个表和很多列）并且 a似乎不正确，因为SELECT ... INTO我必须创建一个TYPE查询具有来自一个表的列和用于距离计算的列。

问题是我目前只使用游标来检查查询是否在我打开和关闭它的循环内返回了某些内容。一旦查询返回某些内容，我就会退出循环并返回查询。我可以马上说，这是我需要的一个丑陋的解决方法。也许有人可以帮助我解决这个问题。

这是一些代码，显示了我目前正在做的事情。

CREATE FUNCTION store_distance(
    latitude double precision,
    longitude double precision,
    radius double precision,
    tries integer
)
RETURNS TABLE(
    store_id store.id%type,
    store_name store.name%type,
    distance double precision
)
AS
$$
DECLARE
    cur_stores CURSOR FOR
        SELECT
            store.id,
            store.name,
            get_distance(latitude, longitude, store.latitude, store.longitude) distance
        FROM
            store
        WHERE
            store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
            AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
        ORDER BY
            distance ASC;
    count int := 0;
    storerow RECORD;
BEGIN
    LOOP
        IF count = tries THEN
            EXIT;
        END IF;
        OPEN cur_stores;
        FETCH cur_stores INTO storerow;
        IF FOUND THEN
            EXIT;
        END IF;
        radius := radius * 2;
        count := count + 1;
        CLOSE cur_stores;
    END LOOP;
    RETURN QUERY
    SELECT
        store.id,
        store.name,
        get_distance(latitude, longitude, store.latitude, store.longitude) distance
    FROM
        store
    WHERE
        store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
        AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
    ORDER BY
        distance ASC;
END;
$$ LANGUAGE PLPGSQL;

所以我的目的是给出坐标、半径和尝试次数，并尝试在该搜索框中找到商店。如果没有找到商店，我将半径加倍并重试，直到查询返回某些内容或达到尝试次数。
该RETURN TABLE部分基本上是因为我想返回距离，所以RETURN SETOF store没有用。

postgresql performance

1 个回答

  1. Best Answer

    我认为您根本不需要光标。要缩短代码，您可以只使用视图。为了提高性能，物化视图应该让你走得更远。Postgres 9.3 具有内置功能，但您可以自己在旧版本中轻松实现它。

    考虑这个简化的形式：

    CREATE FUNCTION store_distance(_lat    double precision
                              ,_long   double precision
                              ,_radius double precision
                              ,_tries  integer)
RETURNS TABLE(
   store_id   store.id%type
  ,store_name store.name%type
  ,distance   double precision) AS
$func$
DECLARE
   _ct  int   := 0;
   _pos point := point(_lat, _long);
BEGIN
   LOOP
      EXIT WHEN _ct >= _tries
           OR   EXISTS (
            SELECT 1 FROM store s
            WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius));

      _radius := _radius * 2;
      _ct     := _ct + 1;
   END LOOP;

   RETURN QUERY
   SELECT s.id, s.name
         ,get_distance(_lat, _long, s.latitude, s.longitude)
   FROM   store s
   WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius);
   ORDER  BY 3;
END
$func$ LANGUAGE plpgsql STRICT;

    我制作了STRICT禁止 NULL 输入的函数，这可能导致无限循环。

    请注意我如何使用带有“包含”运算符而不是box的圆。有人会假设计算比使用盒子稍微贵一些，但是一旦您使用GiST 索引支持您的查询，这根本不重要，例如：<@

    CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));

    您可能会考虑将 lat / lon 存储为point开头并将表达式上的索引替换为列上更简单的索引。无论哪种方式都可以，只需确保查询与索引匹配，以便使用它。大桌子有很大的不同。

    您可能对我去年发布的 SO 上的这个密切相关的答案感兴趣- 有更多解释和链接。

    • 5

相关问题