I’m using PostgreSQL 10.5 and I have the following SQL:
FOR temprow IN
SELECT o.objectid, o.nametag, cor.userseqno, cor.commseqno
FROM "commuserobjectrights" as cor
LEFT JOIN "object" as o ON cor.objectid = o.objectid
WHERE o.nametag LIKE 'commission.video_questions'
LOOP
INSERT INTO u commuserobjectrights (objectid, commseqno, userseqno, access)
VALUES (temprow.objectid, temprow.commseqno, temprow.userseqno, TRUE);
END LOOP;
which throws the following error:
ERROR: syntax error at or near "FOR" Position: 3
I have never used loops before but according the documentation, postgresql should have support for these types of loops. And yes, I have checked and double checked that all tables and column names are spelled correctly.
asked Sep 3, 2018 at 14:38
0
You can’t use FOR loops outside of procedural code. But, in general Postgres (and SQL) is optimized to already do set based operations. So, you may phrase this as an INSERT INTO ... SELECT:
INSERT INTO commuserobjectrights (objectid, commseqno, userseqno, access)
SELECT o.objectid, o.nametag, cor.userseqno, TRUE
FROM "commuserobjectrights" as cor
LEFT JOIN "object" as o ON cor.objectid = o.objectid
WHERE o.nametag LIKE 'commission.video_questions';
answered Sep 3, 2018 at 14:40
Tim BiegeleisenTim Biegeleisen
497k25 gold badges280 silver badges355 bronze badges
3
FOR is procedural code, so you need to use DO or use it in stored code.
DO $$
DECLARE
temprow record ;
BEGIN
FOR temprow IN
SELECT o.objectid, o.nametag, cor.userseqno, cor.commseqno
FROM "commuserobjectrights" as cor
LEFT JOIN "object" as o ON cor.objectid = o.objectid
WHERE o.nametag LIKE 'commission.video_questions'
LOOP
INSERT INTO commuserobjectrights (objectid, commseqno, userseqno, access)
VALUES (temprow.objectid, temprow.commseqno, temprow.userseqno, TRUE);
END LOOP;
END;
$$;
This is not the most efficient way to do this task but for other tasks where you can’t easily write SQL DO may be useful.
answered Sep 3, 2018 at 23:03
JasenJasen
11.7k2 gold badges29 silver badges47 bronze badges
0
I have a problem in postgres function:
CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint)
RETURNS int AS $$
DECLARE catNumber int DEFAULT 0;
DECLARE cat RECORD;
BEGIN
WITH RECURSIVE children(categoryid,category_fk) AS (
SELECT categoryid, category_fk
FROM b2m.category_tab
WHERE categoryid = 1
UNION ALL
SELECT c1.categoryid,c1.category_fk
FROM b2m.category_tab c1, children
WHERE children.categoryid = c1.category_fk
)
FOR cat IN SELECT * FROM children LOOP
IF EXISTS (SELECT 1 FROM b2m.repoobject_tab WHERE category_fk = cat.categoryid) THEN
catNumber = catNumber +1
END IF;
END LOOP;
RETURN catNumber;
END;
$$ LANGUAGE 'plpgsql';
I’ve got error:
FEHLER: Syntax error near »FOR«
LINE 1: ...dren WHERE children.categoryid = c1.category_fk ) FOR $2 I...
Remi Guan
21.4k17 gold badges63 silver badges87 bronze badges
asked May 15, 2010 at 8:33
The syntax error is because it is expecting the WITH ... to be followed by SELECT where it found FOR.
I haven’t used WITH clauses in queries in plpgsql, but I’d try moving FOR cat IN to before the WITH, which is part of the query.
answered May 15, 2010 at 9:07
Stephen DenneStephen Denne
36.1k10 gold badges45 silver badges60 bronze badges
0
Try this:
CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint) RETURNS int AS
$$
DECLARE catNumber int DEFAULT 0;
DECLARE cat RECORD;
BEGIN
FOR cat IN
WITH RECURSIVE children(categoryid,category_fk) AS (
SELECT categoryid, category_fk
FROM b2m.category_tab
WHERE categoryid = 1
UNION ALL
SELECT c1.categoryid,c1.category_fk
FROM b2m.category_tab c1, children
WHERE children.categoryid = c1.category_fk
)
SELECT * FROM children
LOOP
IF EXISTS (SELECT 1 FROM b2m.repoobject_tab WHERE category_fk = cat.categoryid) THEN
catNumber = catNumber +1
END IF;
END LOOP;
RETURN catNumber;
END;
$$ LANGUAGE 'plpgsql';
I noticed your query hardly needs looping, just do this:
CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint) RETURNS int AS
$$
BEGIN
RETURN
(
SELECT COUNT(*) FROM b2m.repoobject_tab WHERE category_fk IN
(
WITH RECURSIVE children(categoryid,category_fk) AS
(
SELECT categoryid, category_fk
FROM b2m.category_tab
WHERE categoryid = 1
UNION ALL
SELECT c1.categoryid,c1.category_fk
FROM b2m.category_tab c1, children
WHERE children.categoryid = c1.category_fk
)
SELECT categoryid FROM children
)
);
END;
$$ LANGUAGE 'plpgsql';
answered May 15, 2010 at 9:21
Michael BuenMichael Buen
38.5k9 gold badges92 silver badges118 bronze badges
1
I have a problem in postgres function:
CREATE OR REPLACE FUNCTION linkedRepoObjects(id bigint)
RETURNS int AS $$
DECLARE catNumber int DEFAULT 0;
DECLARE cat RECORD;
BEGIN
WITH RECURSIVE children(categoryid,category_fk) AS (
SELECT categoryid, category_fk
FROM b2m.category_tab
WHERE categoryid = 1
UNION ALL
SELECT c1.categoryid,c1.category_fk
FROM b2m.category_tab c1, children
WHERE children.categoryid = c1.category_fk
)
FOR cat IN SELECT * FROM children LOOP
IF EXISTS (SELECT 1 FROM b2m.repoobject_tab WHERE category_fk = cat.categoryid) THEN
catNumber = catNumber +1
END IF;
END LOOP;
RETURN catNumber;
END;
$$ LANGUAGE 'plpgsql';
I’ve got error:
FEHLER: Syntax error near »FOR«
LINE 1: ...dren WHERE children.categoryid = c1.category_fk ) FOR $2 I...
I am trying to create the following procedure on postgresql
CREATE PROCEDURE run_all_scheduled_tasks()
LANGUAGE plpgsql
AS
$$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT * FROM some_table LOOP
-- Statement 1..2..3..4
EXCEPTION
WHEN others THEN
RAISE NOTICE 'Error Occured! Code: %, Messege:%', sqlstate, sqlerrm;
ROLLBACK;
COMMIT;
END LOOP;
END;
$$;
I get the following error:
ERROR: syntax error at or near «EXCEPTION»
LINE 12: EXCEPTION
It works properly if I shift exception block outside the for loop.
What I want to achieve is that for one iteration of loop, either all statements from 1..4 should get executed or none of them. If an error occurs, I still want to preserve the changes made up till previous iteration and then carry on to next iteration by simply raising a notice for the error.
What is causing this error?
I am getting an error for the below FOR loop with cursor in a function:
ERROR: syntax error at or near "AS"
CREATE OR REPLACE FUNCTION functionName(custom varchar(15)) RETURNS INTEGER AS $$
DECLARE
...
BEGIN
...
FOR loop AS cursor CURSOR FOR
SELECT column FROM table
DO
...
END FOR;
RETURN someValue;
END;
$$
LANGUAGE plpgsql;
asked Nov 16, 2015 at 6:11
1
This is wrong syntax — Postgres doesn’t support declaration of CURSOR inside FOR statement. See documentation:
CREATE OR REPLACE FUNCTION foo()
RETURNS void AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT xx,yy FROM some_tab
LOOP
RAISE NOTICE 'row data: %', r;
END LOOP;
END;
$$ LANGUAGE plpgsql;
It looks so you are using ANSI SQL PSM syntax. PL/pgSQL is based on PL/SQL syntax (Oracle/ADA).
answered Nov 17, 2015 at 4:22
Pavel StehulePavel Stehule
41.7k5 gold badges90 silver badges93 bronze badges