Thread: Declaring multidimensional arrays in pl/pgsql
Hi, all. I was wondering, can I really declare a 2-dimensional array of arbitrary size in pl/pgsql? According to the docs it seems that only way would be to declare it as something like : myArray := ARRAY[[1,2], [3,4], [5,6]]; But what if I pass the dimensions as function parameters? My postgresql version is 8.1. Thanks.
On Nov 29, 2007 3:34 AM, Max Zorloff <zorloff@gmail.com> wrote: > According to the docs it seems that only way would be to declare it as > something like : > myArray := ARRAY[[1,2], [3,4], [5,6]]; You can declare arbitrary-sized, n-dimensional arrays: ... DECLARE myArray integer[][]; -- two-dimensional integer array BEGIN ... END; ... See: http://www.postgresql.org/docs/8.1/static/arrays.html
I wrote: > You can declare arbitrary-sized, n-dimensional arrays: Sorry, I re-read your post. You want to programatically define the array dimensions depending on function arguments. You could try building a string, then casting to the correct array type (not tested).
On Thu, 29 Nov 2007 18:11:22 +0400, Rodrigo De León <rdeleonp@gmail.com> wrote: > On Nov 29, 2007 3:34 AM, Max Zorloff <zorloff@gmail.com> wrote: >> According to the docs it seems that only way would be to declare it as >> something like : >> myArray := ARRAY[[1,2], [3,4], [5,6]]; > > You can declare arbitrary-sized, n-dimensional arrays: > ... > DECLARE > myArray integer[][]; -- two-dimensional integer array > BEGIN > ... > END; > ... > > See: > http://www.postgresql.org/docs/8.1/static/arrays.html I can. But unfortunately : create or replace function testfunc() returns setof record as $$ DECLARE myArray int[][]; BEGIN FOR i IN 1..10 LOOP FOR j IN 1..10 LOOP RAISE NOTICE '% %', i, j; myArray[i][j] := 1; END LOOP; END LOOP; RETURN; END $$ language plpgsql; ponline=# select testfunc(); NOTICE: 1 1 NOTICE: 1 2 ERROR: invalid array subscripts КОНТЕКСТ: PL/pgSQL function "testfunc" line 7 at assignment 2-dimensional arrays do not grow like 1-dimensional do (it says so in the docs). The initial array is 1x1 size. I suppose I'm stuck with emulating 2-dim arrays through 1-dim arrays because I also need them to grow later.
On Thu, 29 Nov 2007 19:21:03 +0400, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > arrays in PostgreSQL have to be regular allways. And before 8.3 array > cannot contais NULL, so you cannot simpl resize two dim array :(. But > your functions doesn't work in 8.3. too. So you can > > a) use 1D array and access to array like myarray[10*(n1-1)+n2] > > b) init array with string like > > create or replace function testfunc() > returns void as $$ > DECLARE > myArray int[][]; > BEGIN > myArray := ('{'||array_to_string(array(select > '{0,0,0,0,0,0,0,0,0,0}'::text from > generate_series(1,10)),',')||'}')::int[][]; > FOR i IN 1..10 LOOP > FOR j IN 1..10 LOOP > RAISE NOTICE '% %', i, j; > myArray[i][j] := 1; > END LOOP; > END LOOP; > RETURN; > END > $$ language plpgsql; Thanks for the info, but running the above gives me that : ponline=# select testfunc(); ERROR: cannot cast type text to integer[] CONTEXT: SQL statement "SELECT ('{'||array_to_string(array(select '{0,0,0,0,0,0,0,0,0,0}'::text from generate_series(1,10)),',')||'}')::int[][]" PL/pgSQL function "testfunc" line 4 at assignment I think 8.1 does not have text -> int[] cast available. I think I'm stuck with option a.
On Thu, 29 Nov 2007 21:15:50 +0400, Rodrigo De León <rdeleonp@gmail.com> wrote: > On Nov 29, 2007 9:33 AM, Max Zorloff <zorloff@gmail.com> wrote: >> I don't think that works. >> >> ponline=# select ('{1,2,3}'::text)::int[]; >> ERROR: cannot cast type text to integer[] > > Can you try: > > select ('{1,2,3}'::unknown)::int[]; Thanks, that works fine with 2-dim arrays too.