Create array dynamically inside begin and end

Question

How to create an array dynamically inside PostgreSQL?

Consider this e.g.:

CREATE OR REPLACE FUNCTION fun( )
RETURNS SETOF void AS
$BODY$
DECLARE

i numeric;

BEGIN

 FOR i in 1..10   LOOP
     //I have to create an array as 
     arr1[] ,arr2[] ... based on length
 END LOOP;
END;

$BODY$
LANGUAGE plpgsql

so do you want to create array dynamically, loop over array? It's not clear (for me) from your example. You can use array_append to add element in array if that's what you need - postgresql.org/docs/current/static/functions-array.html — roman
– roman, Commented Aug 28, 2013 at 11:36

Pavel Stehule · Accepted Answer · 2013-08-28 13:02:17Z

9

There is a special function for this purpose - array_fill:

postgres=# select array_fill(0, ARRAY[10]);
      array_fill       
-----------------------
 {0,0,0,0,0,0,0,0,0,0}
(1 row)

postgres=# select array_fill('Hello'::text, ARRAY[10]);
                          array_fill                           
---------------------------------------------------------------
 {Hello,Hello,Hello,Hello,Hello,Hello,Hello,Hello,Hello,Hello}
(1 row)

postgres=# select array_fill(0, ARRAY[3,3]);
        array_fill         
---------------------------
 {{0,0,0},{0,0,0},{0,0,0}}
(1 row)

In PL/pgSQL (but it is significantly slower for large arrays (over 100 items):

DO $$
DECLARE
result int[] = '{}';
BEGIN
  FOR i IN 1..10
  LOOP
    result := result || 0;
  END LOOP;
  RAISE NOTICE '%', result;
END;
$$;

edited Aug 28, 2013 at 13:02

answered Aug 28, 2013 at 12:10

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2659199 Over a year ago

I dont want to store the values into array. I want to create array as arr1[] ,arr2[], arr3[],arr4[] likewise n number of times.

Pavel Stehule Over a year ago

sorry, I don't understand. Why you do it? What do you do?

user2659199 Over a year ago

using for loop i have to create array. If for loop is executed 4 times then i hav to create 4 arrays as arr1,arr2,arr3,arr4.

Pavel Stehule Over a year ago

and what you want to do with array later? You have to move or save this data somewhere?

Pavel Stehule Over a year ago

you would to create array of arrays? It is not possible directly in Postgres - it support only multidimensional arrays - you can use a trick with nested type: create type x as (a int[]); select ARRAY[ROW(ARRAY[10,20]), ROW(ARRAY[10,20,40])]::x[]; But this technique is little bit obscure - and probably using a temporary table is better

|

roman · Accepted Answer · 2013-08-29 08:52:29Z

It's really hard to say which way you should go without knowing your final goal, Pavel Stehule gave you good advice about filling arrays, you can use temporary table to store arrays inside your function.
You can also create a function which returns a set of arrays and then iterate through it, like:

create or replace function fun1()
returns setof int[] as
$BODY$
declare
    i int;
begin
    for i in 1..10 loop
        return next array_fill(0, array[i]);
    end loop;
end;
$BODY$
language plpgsql;


create or replace function fun2()
returns setof int as
$BODY$
declare
    a int[];
begin
    for a in select * from fun1() loop
        return next array_length(a, 1);
    end loop;
end;
$BODY$
language plpgsql;

sql fiddle frmo

Collectives™ on Stack Overflow

Create array dynamically inside begin and end

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related