CODEWITHSUNDEEP
sqlpostgresqlpostgresql-8.4
user2569524
user2569524

Reputation: 1751

How to initialize an array in postgres inside a loop

I am trying to initialize an array array_entries. Tries array_fill as array_fill(0,array_entries) but dint work. 

create or replace function vin_temp_test1(k date,x varchar) RETURNS float AS $$
    declare
    array_entries int [];
    curs4  CURSOR FOR  select * from temp_table;
    record_type1 record;

    fetch curs4 into record_type1;
            exit when not found;
    loop
    -- trying to intialize the array array_entries here
        loop
     --filling the array inside this loop.
        end loop;
    end loop;

Upvotes: 3

Views: 7265

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45795

Probably you have NULL in array_entries

postgres=# select array_fill(0, NULL);
ERROR:  dimension array or low bound array cannot be null
postgres=# select array_fill(0, ARRAY[10]);
      array_fill       
-----------------------
{0,0,0,0,0,0,0,0,0,0}
(1 row)

Attention!

Is good to know, so update of large array (larger than 20000 fields) is pretty slow. So much faster than repeatable update is using ARRAY(subselect) constructor

postgres=# DO $$ DECLARE x int[]; 
           begin  
              x := array_fill(0,ARRAY[100000]); 
              for i in 1..100000 loop 
                x[i] := 1; 
           end loop; end $$;
DO
Time: 5533.581 ms
postgres=# DO $$ DECLARE x int[]; 
           begin  x := ARRAY(SELECT 1 FROM generate_series(1,100000)); end $$;
DO
Time: 36.590 ms

Upvotes: 2

Related Questions