CODEWITHSUNDEEP
sqlpostgresqlcursorplpgsqlwindow-functions
Alex H.
Alex H.

Reputation: 53

plpgsql syntax error at or near ";"

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:

Database: PostgreSQL 9.1
Editor: pgAdmin III Query Tool

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.

Upvotes: 3

Views: 5506

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659367

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.

  • 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.

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

Set-based solution

The whole procedural approach is inferior to a set-based approach with data-modifying 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?

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

Upvotes: 7

Related Questions