Reputation: 53
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
Reputation: 659367
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$;
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.
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