plpgsql语法错误在或接近“;”

I have viewed this similar question, but I think my problem may be different. If I am understanding their question correctly, the problem was caused by syntax errors upstream.
In my case, the syntax error is so close to the beginning of the program that it gives me a narrow window of what could have gone wrong, and yet everything looks okay to my eye.

我也遇到过类似的问题，但我认为我的问题可能不同。如果我正确地理解了他们的问题，那么问题是由上游的语法错误引起的。在我的例子中，语法错误非常接近于程序的开头，它给了我一个可能出错的窗口，但是我的眼睛看起来都没问题。

Code:

代码:

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:

错误:

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

I followed these resources almost exactly while writing this script:

我在写这个剧本的时候几乎完全遵循了这些资源:

• PostgreSQL Documentation: Cursors
• PostgreSQL文档:游标
• PostgreSQL Documentation: DO
• PostgreSQL文档:

Database: PostgreSQL 9.1
Editor: pgAdmin III Query Tool

数据库:PostgreSQL 9.1编辑器:pgAdmin III查询工具

I apologize in advance if I am missing something very obvious. I have been staring at this script all day so my brains may be a little scrambled.

如果我错过了一些很明显的东西，我提前道歉。我一整天都在盯着这个脚本，所以我的脑子有点乱。

1 个解决方案

#1

Procedural solution

There was a number of problems in your code.
This should work, and faster, too:

您的代码中有许多问题。这应该奏效，而且更快:

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$;

Major points

• FETCH syntax was incorrect.

  获取语法是不正确的。

• There is no COMMIT in a DO statement. The whole thing runs inside a single transaction automatically, just like a function.

  DO语句中没有提交。整个事务自动地在一个事务中运行，就像一个函数一样。

• You had no condition to terminate your loop.

  您没有条件终止您的循环。

• Explicit cursors are hardly ever necessary. Use the much more convenient (and typically faster) implicit cursor of a FOR loop.

  显式游标几乎没有必要。使用FOR循环的更方便(通常更快)的隐式游标。

• I would advice against CaMeL case identifiers in Postgres. Use legal, lower-case identifiers exclusively.

  我建议在Postgres中对驼峰情况标识。只使用合法的小写标识符。

Set-based solution

The whole procedural approach is inferior to a set-based approach with data-modifying CTEs:

整个程序方法不如基于集合的方法，数据修改CTEs:

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;

Or, if you have concurrent write load, use FOR UPDATE to avoid race conditions:

或者，如果您有并发的写负载，请使用更新以避免竞争条件:

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;

More on SELECT ... FOR UPDATE in CTEs:
Should I include SELECTs in a transaction?

更多选择……CTEs中的更新:是否应该在事务中包含select ?

Similar question / answer:
How to improve performance of a function with cursors in PostgreSQL?

类似的问题/答案:如何改进PostgreSQL中带有游标的函数的性能?