'ERROR: syntax error at or near "AS"' for a PL/pgSQL function with FOR LOOP

Question

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;

Where in the manual did you find that syntax?

user330315
– user330315

2015-11-17 09:24:29 +00:00
Commented Nov 17, 2015 at 9:24 — user330315
– user330315, Commented Nov 17, 2015 at 9:24

Pavel Stehule · Accepted Answer · 2015-11-17 09:18:59Z

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

edited Nov 17, 2015 at 9:18

answered Nov 17, 2015 at 4:22

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

'ERROR: syntax error at or near "AS"' for a PL/pgSQL function with FOR LOOP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related