CODEWITHSUNDEEP
postgresqlplpgsql
user3323258
user3323258

Reputation: 71

PostgreSQL PL/pgSQL syntax error with FOREACH loop

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!

Upvotes: 2

Views: 25672

Answers (1)

user330315
user330315

Reputation:

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.

  1. you should give your parameter a name (not an error, but good coding style)
  2. 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
  3. there is no need for a dynamic SQL to return a value, you can return the variable directly.
  4. 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().

Upvotes: 9

Related Questions