FOR循环declare节中存在语法错误

Question

我试图在Postgres中使用FOR循环来计算数据库中每个地理位置（变量）范围内的数据平均值。我正在使用python/psycopg2。代码如下：

query ='''
DECLARE geoids RECORD;
BEGIN
    RAISE NOTICE 'Beginning average calculation';
    FOR geoids IN select*from census_blocks
        WHERE ST_contains((select geom from census_cbsa
             WHERE cbsafp10='%s'),census_blocks.geom)
        LOOP
        INSERT INTO temp_avgs VALUES
            (geoids,
            select avg(select alljobs from accessibility_results
            WHERE geoid=geoids
                AND deptime BETWEEN '%s' and '%s'
                AND threshold='%s')
        END LOOP;
END;
'''

我收到的错误是

^{pr2}$

如果我删除这个DECLARE语句（因为我相信选择值上的LOOP变量会自动声明为RECORD），则错误变成：

psycopg2.ProgrammingError: syntax error at or near "RAISE"
LINE 4:   RAISE NOTICE 'Beginning average calculation';

我应该如何正确格式化这个查询？在

Answer 1

循环

您使用的是PL/pgSQL代码，但试图将其表述为SQL查询。那是不可能的。在

使用^{}语句或（因为您希望使用参数）创建一个plpgsql function：

CREATE OR REPLACE FUNCTION foo(_cbsafp10     ?type?       replace with ...
                              ,_deptime_from timestamp?   ... actual data types
                              ,_deptime_to   timestamp?
                              ,_threshold    ?type?)
  RETURNS void AS
$func$
DECLARE
   rec RECORD;
BEGIN

FOR rec IN
   SELECT b.*
   FROM   census_blocks b
   JOIN   census_cbsa   c ON ST_contains(c.geom, b.geom)
   WHERE  c.cbsafp10 = _cbsafp10
LOOP
   INSERT INTO temp_avgs      you might add a target list for safety. depends ..
   SELECT rec.*, avg(alljobs)
   FROM   accessibility_results
   WHERE  geoid = rec.geoid   assuming you join on column "geoid"?
   AND    deptime BETWEEN _deptime_from AND _deptime_to
   AND    threshold = _threshold;
END LOOP;

END
$func$ LANGUAGE plpgsql;

确保你在你的客户中正确地转义引号！在

• 选择值上的循环变量是而不是自动声明为任何内容。在
• 替换不必要的子查询。在
• 第二个错误消息的直接原因是：^{}是一个plpgsql命令，不是是一个SQL命令。在

卓越的基于集合的解决方案

这将演示plpgsql函数的基础知识。但是，正如@Gordon已经提供的那样，只需使用一个INSERT语句来执行相同的。进一步解开：

^{pr2}$

Answer 2

不如把它当作一个单选词。像这样：

INSERT INTO temp_avgs(geoid, average)
   select cb.geoid, avg(aj.alljobs)
   from census_blocks cb join
        accessibility_results ar
        on cb.geoid = ar.geoid
   WHERE ST_contains((select geom from census_cbsa WHERE cbsafp10='%s'), cb.geom) and
         ar.deptime BETWEEN '%s' and '%s' AND ar.threshold='%s'
   group by cb.geoid;

我不确定所有列的来源，所以表别名有点猜测。在