PostgreSQL PL/pgSQL syntax error with FOREACH loop

Question

I'm trying to learn PL/pgSQL by writing some simple programs. To learn about FOREACH loop, I wrote the following:

CREATE OR REPLACE FUNCTION test(int[]) RETURNS void AS $$  
DECLARE                                                     
    window INT;                                             
BEGIN                                                       
    FOREACH window IN ARRAY $1                              
    LOOP                                                    
        EXECUTE 'SELECT $1' USING window;                   
    END LOOP;                                               
$$ LANGUAGE plpgsql;                                        

SELECT test(ARRAY [30,60]);

I expect that this code snippet would first print 30 and then 60. However, I get the following error.

psql:loop.sql:11: ERROR:  syntax error at end of input
LINE 7:         EXECUTE 'SELECT $1' USING window;
                                                ^
psql:loop.sql:13: ERROR:  function test(integer[]) does not exist
LINE 1: SELECT test(ARRAY [30,60]);
               ^
HINT:  No function matches the given name and argument types. You might need
 to add explicit type casts.

So the function definition has a syntax error, but I don't understand what the error is and how to fix it. I'd appreciate any help. Thanks!

Erwin Brandstetter · Accepted Answer · 2019-03-15 16:34:52Z

9

Your function is declared as returns void so you can't return anything from it. If you want to return multiple values, you need to use returns setof integer

But it has more problems than that.

you should give your parameter a name (not an error, but good coding style)
to return a value from a function you need to use return. To return multiple values (because of returns setof) you need to use return next
there is no need for a dynamic SQL to return a value, you can return the variable directly.
Also not not an error, but: window is a keyword, I wouldn't use a variable with that name.

Applying all that, your function should look like this:

CREATE OR REPLACE FUNCTION test(p_input int[]) 
  RETURNS setof integer
as
$$  
DECLARE                                                     
  l_value INT;                                             
BEGIN                                                       
    FOREACH l_value IN ARRAY p_input                             
    LOOP                                                    
        return next l_value;
    END LOOP;                                               
end;
$$ 
LANGUAGE plpgsql;

I am not sure if you are aware, but there is already a built-in function which achieves the same thing: unnest().

edited Mar 15, 2019 at 16:34

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Mar 13, 2019 at 14:06

user330315

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

2 Comments

user3323258 Over a year ago

Thanks for helping :) I need to get EXECUTE... USING to work as well. Do you know what's the issue with EXECUTE... USING in my code?

user3323258 Over a year ago

Update: it's because of the WINDOW keyword. It works after I changed the variable name.

Collectives™ on Stack Overflow

PostgreSQL PL/pgSQL syntax error with FOREACH loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related