sql - plpgsql语法错误在“;”附近

我看过类似的问题，但我认为我的问题可能不同。如果我正确地理解了他们的问题，那么问题是由上游的语法错误引起的。
在我的例子中，语法错误非常接近程序的开头，它给了我一个很窄的窗口，告诉我可能出了什么问题，但是在我看来一切都很好。
代码：

DO $$
DECLARE topic_cursor CURSOR FOR SELECT * FROM "socialMediaModel_topic" WHERE "active_search"=True;

BEGIN
OPEN topic_cursor;
FETCH FIRST FROM topic_cursor;
LOOP
    SELECT "topic" FROM topic_cursor AS "c_topic";
    SELECT "topic_id" FROM topic_cursor AS "c_id";
    SELECT "active_search" FROM topic_cursor AS "c_active";

    INSERT INTO "socialMediaModel_datacollection" ("name", "active")
        VALUES (c_topic, c_active);
    INSERT INTO "socialMediaModel_datacollectiontopic" ("data_collection_id_id", "topic_id_id")
        VALUES ((SELECT "data_collection_id" FROM "DataCollection" where name=c_topic), c_id);

    FETCH NEXT FROM topic_cursor;

END LOOP;
CLOSE topic_cursor;

UPDATE "socialMediaModel_topic" SET "active_search" = False WHERE "active_search"=True;
COMMIT;
END$$;

错误：

ERROR:  syntax error at or near ";"
LINE 9:  FETCH FIRST FROM topic_cursor;
                                  ^
********** Error **********

ERROR: syntax error at or near ";"
SQL state: 42601
Character: 247

在编写脚本时，我几乎完全遵循了这些资源：
this
PostgreSQL Documentation: Cursors
数据库：PostgreSQL 9.1
编辑器：pgAdmin III查询工具
如果我遗漏了一些很明显的东西，我会提前道歉。我整天都在看这个剧本，所以我的脑子可能有点乱。

最佳答案

程序解决方案
你的代码有很多问题。
这应该会奏效，而且速度也会更快：

DO
$do$
DECLARE
   rec record;
BEGIN
   FOR rec IN
      SELECT s.*, d.data_collection_id
      FROM   "socialMediaModel_topic" s
      LEFT   JOIN "DataCollection"    d ON d.name = s.topic
      WHERE  active_search
   LOOP
      INSERT INTO "socialMediaModel_datacollection" (name, active)
      VALUES (rec.topic, rec.active_search);
      INSERT INTO "socialMediaModel_datacollectiontopic"
                              (data_collection_id_id, topic_id_id)
      VALUES (rec.data_collection_id, rec.topic_id);
   END LOOP;

   UPDATE "socialMediaModel_topic"
   SET    active_search = FALSE
   WHERE  active_search;
END
$do$;

要点
FETCH syntax不正确。
COMMIT语句中没有DO。整个事务自动运行在单个事务中，就像一个函数一样。
你没有条件终止循环。
几乎不需要显式游标。使用FOR loop的更方便（通常更快）的隐式光标。
我建议不要在Postgres中使用CaMeL大小写标识符。只使用合法的小写标识符。
基于集合的解决方案
整个过程方法不如基于集合的方法：

WITH ins1 AS (
   INSERT INTO "socialMediaModel_datacollection" (name, active)
   SELECT topic, active_search
   FROM   "socialMediaModel_topic"
   WHERE  active_search
   )
, ins2 AS (
   INSERT INTO "socialMediaModel_datacollectiontopic"
                           (data_collection_id_id, topic_id_id)
   SELECT d.data_collection_id, s.topic_id
   FROM   "socialMediaModel_topic" s
   LEFT   JOIN "DataCollection"    d ON d.name = s.topic
   WHERE  s.active_search
   )
UPDATE "socialMediaModel_topic"
SET    active_search = FALSE
WHERE  active_search;

或者，如果有并发写负载，请使用FOR UPDATE以避免竞争条件：

WITH sel AS (
   SELECT s.topic_id, s.topic, s.active_search, d.data_collection_id
   FROM   "socialMediaModel_topic" s
   LEFT   JOIN "DataCollection"    d ON d.name = s.topic
   WHERE  s.active_search
   FOR    UPDATE
   )
, ins1 AS (
   INSERT INTO "socialMediaModel_datacollection" (name, active)
   SELECT topic, active_search FROM sel
   )
, ins2 AS (
   INSERT INTO "socialMediaModel_datacollectiontopic"
                           (data_collection_id_id, topic_id_id)
   SELECT d.data_collection_id, s.topic_id FROM sel
   )
UPDATE "socialMediaModel_topic"
SET    active_search = FALSE
WHERE  active_search;

关于CTEs中SELECT ... FOR UPDATE的更多信息：
data-modifying CTEs
类似的问题/答案：
Should I include SELECTs in a transaction?