<h3>循环</h3>
<p>您使用的是PL/pgSQL代码,但试图将其表述为SQL查询。那是不可能的。在</p>
<p>使用<a href="http://www.postgresql.org/docs/current/interactive/sql-do.html" rel="nofollow"><strong>^{<cd1>}</strong></a>语句或(因为您希望使用参数)创建一个<a href="http://www.postgresql.org/docs/current/interactive/sql-createfunction.html" rel="nofollow">plpgsql function</a>:</p>
<pre><code>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;
</code></pre>
<p>确保你在你的客户中正确地转义引号!在</p>
<ul>
<li>选择值上的循环变量是<strong><em>而不是</em></strong>自动声明为任何内容。在</li>
<li>替换不必要的子查询。在</li>
<li>第二个错误消息的直接原因是:<a href="http://www.postgresql.org/docs/current/interactive/plpgsql-errors-and-messages.html" rel="nofollow">^{<cd2>}</a>是一个plpgsql命令,<em>不是</em>是一个SQL命令。在</li>
</ul>
<h3>卓越的基于集合的解决方案</h3>
<p>这将演示plpgsql函数的基础知识。但是,正如@Gordon已经提供的那样,只需使用一个<code>INSERT</code>语句来执行相同的</em>。进一步解开:</p>
^{pr2}$