I am trying to write a very simple pgsql statement to loop through a simple array of state abbreviations.

 CREATE OR REPLACE FUNCTION my_schema.showState() 
 RETURNS text AS 
 $$
 DECLARE
 my_array text[] := '["az","al", "ak", "ar"]' 
 BEGIN
     FOREACH state IN my_array 
     LOOP
         RETURN SELECT format('%s', state);
     END LOOP;
 END;
 $$ LANGUAGE plpgsql;

SELECT * FROM showState();

I am using PostgresSQL version 11 . I keep getting an error ERROR: syntax error at or near "BEGIN" The output I want to see here is just seeing the state abbreviation printed in the results window for now. Like this:

What am I doing wrong here?

CodePudding user response：

• There's a ; missing after my_array text[] := '["az","al", "ak", "ar"]'.
• '["az","al", "ak", "ar"]' isn't a valid array literal.
• If you want a set returning function, you need to declare its return type as a SETOF.
• The ARRAY keyword is missing in the FOREACH's head.
• state must be declared.
• You need to use RETURN NEXT ... to push a value into the set to be returned.
• format() is pointless here, it doesn't effectively do anything.

With all that rectified one'd get something along the lines of:

CREATE
 OR REPLACE FUNCTION showstate()
                     RETURNS SETOF text
AS 
$$
DECLARE
  my_array text[] := ARRAY['az',
                           'al',
                           'ak',
                           'ar'];
  state text;
BEGIN
  FOREACH state IN ARRAY my_array 
  LOOP
    RETURN NEXT state;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

db<>fiddle