Menu
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?
891
The syntax of the for loop statement to iterate over a result set of a dynamic query: [ <<label>> ] for row in execute query_expression [ using query_param [, ... ] ] loop statements end loop [ label ];
Postgresql loop insert In Postgresql, the loop can be used to insert data into the table, suppose we want to insert some kind of data, again and again, then we can use the loop. Let' create a table named dummy. CREATE TABLE dummy(id int); The above code will create a new table named dummy with one column named id.
Procedural elements like loops are not part of the SQL language and can only be used inside the body of a procedural language function, procedure (Postgres 11 or later) or a DO statement, where such additional elements are defined by the respective procedural language.
DO $do$ DECLARE a integer[] := array[1,2,3]; i integer; -- int, not bigint BEGIN FOR i IN 1 .. array_upper(a, 1) LOOP RAISE NOTICE '%', a[i]; -- single quotes END LOOP; END $do$; Or simpler with FOREACH in PostgreSQL 9.1 or later:
FOREACH i IN ARRAY a LOOP RAISE NOTICE '%', i; END LOOP; For multi-dimensional arrays see:
However, set-based solutions with generate_series() or unnest() are often faster than looping over big sets. Basic examples:
Search the tags generate-series or unnest for more.
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
