使用Pl/pgSQL在循环中向查询传递参数

Question

我正在尝试创建一个参数查询，该查询将使用plpgsql在循环中执行。

这两个参数将是传递到查询中的起始纬度/经度点。纬度和经度点将从数组中检索-因此它将在数组上进行类似foreach的迭代，并在每次执行时将两个经度/经度值传递到查询中。

我怎么能做这样的事情呢？

我的查询和我认为的实现方式是这样的：( X，Z值是要传递的参数纬度/经度值，Y和T将通过执行某种计算得到，我将处理)。为了清楚起见，我的查询返回多行和多列的结果(时间戳和计数)，因为它计算并返回落入小时间隔的请求数(时间戳)

for each latitude-longitude pair X,Z in array

find Y and T from X and Z

WITH cal AS (
    SELECT generate_series('2011-02-02 00:00:00'::timestamp ,
                           '2012-04-01 05:00:00'::timestamp , 
                           '1 hour'::interval) AS stamp
),
qqq AS (
  SELECT date_trunc('hour', calltime) AS stamp, count(*) AS zcount
  FROM mytable
  WHERE calltime >= '2011-02-13 22:55:11' 
    AND calltime <= '2012-02-13 01:02:21'
    AND (calltime::time >= '22:55:11' 
    OR calltime::time <= '01:02:21')
    AND lat >= X 
    AND lat <= Y
    AND lon >= Z
    AND lon <= T
 GROUP BY date_trunc('hour', calltime)
)
SELECT cal.stamp, COALESCE (qqq.zcount, 0) AS zcount
FROM cal
LEFT JOIN qqq ON cal.stamp = qqq.stamp
WHERE cal.stamp >= '2011-02-13 22:00:00' 
  AND cal.stamp <= '2012-02-13 01:02:21' 
  AND (
    extract ('hour' from cal.stamp) >= extract ('hour' from '2011-02-13 22:00:00'::timestamp) or
    extract ('hour' from cal.stamp) <= extract ('hour' from '2012-02-13 01:02:21'::timestamp) 
  )
ORDER BY stamp ASC;

Answer 1

• 将数组传递给函数，并对数组中的元素进行循环，其中r是位于函数的DECLARE子句中的record变量；或者通过传递给函数的DECLARE游标进行
• 循环。请参阅PL/PgSQL cursors.

更好的是，避免使用LOOP，并将SELECT unnest(thearray)集成到您的CTE中。PL/PgSQL中的循环比SQL CTE中的等效循环慢得多。

参见unnest，LOOP。