I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this:
declare a integer[] = array[1,2,3]; i bigint; begin for i in a loop raise notice "% ",i; end loop; return true; end In my actual use case the integer array a is passed as parameter to the function. I get this error:
ERROR: syntax error at or near "$1" LINE 1: $1
How to loop through the array properly?
Postgres 9.1 added FOREACH to loop over arrays:
DO $do$ DECLARE _arr int[] := '{1,2,3,4}'; _elem int; -- matching element type BEGIN FOREACH _elem IN ARRAY _arr LOOP RAISE NOTICE '%', _elem; END LOOP; END $do$; db<>fiddle here
Works for multi-dimensional arrays, too: Postgres flattens the array and iterates over all elements. To loop over slices, see link below.
For older versions:
FOR i IN 1 .. array_upper(_arr, 1) -- "i" is the index LOOP RAISE NOTICE '%', _arr[i]; -- single quotes END LOOP; For multi-dimensional arrays and looping over array slices see:
However, set-based solutions with generate_series() or unnest() are often faster than looping over big sets. Basic examples:
f(array[1,2,3,4]) is there any better way to pass an array to a function ?
f('{1,2,3,4}'::int[]) is another way. Better? - you decide!
format() and EXECUTE in illegal ways. Proper syntax: EXECUTE format('DROP TABLE IF EXISTS threedeeinventorysync.%I;', 'partition_location_' || _elem); Example: stackoverflow.com/a/12082038/939860You can use a FOREACH statement to iterate the 1D array of INT[] as shown below:
DO $$ DECLARE temp INT; _1d_arr INT[] := ARRAY[1,2,3]; BEGIN FOREACH temp SLICE 0 IN ARRAY _1d_arr LOOP RAISE INFO '%', temp; END LOOP; END $$; Or, you can use a FOR statement to iterate the 1D array of INT[] as shown below:
DO $$ DECLARE _1d_arr INT[] := ARRAY[1,2,3]; BEGIN FOR num IN 1..3 LOOP RAISE INFO '%', _1d_arr[num]; END LOOP; END $$; Then, you can iterate the 1D array of INT[] as shown below:
INFO: 1 INFO: 2 INFO: 3 DO 
