Lists: | pgsql-general |
---|
From: | "Max Zorloff" <zorloff(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Declaring multidimensional arrays in pl/pgsql |
Date: | 2007-11-29 08:34:03 |
Message-ID: | op.t2joq1hill0p5y@1-rtt202dnf3uds.mshome.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Rodrigo De León <rdeleonp(at)gmail(dot)com> |
---|---|
To: | "Max Zorloff" <zorloff(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Declaring multidimensional arrays in pl/pgsql |
Date: | 2007-11-29 14:11:22 |
Message-ID: | a55915760711290611l6296d4dcic05fe54ac95b7e41@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Nov 29, 2007 3:34 AM, Max Zorloff <zorloff(at)gmail(dot)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;
...
From: | Rodrigo De León <rdeleonp(at)gmail(dot)com> |
---|---|
To: | "Max Zorloff" <zorloff(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Declaring multidimensional arrays in pl/pgsql |
Date: | 2007-11-29 14:19:51 |
Message-ID: | a55915760711290619l271c47fdp4733a69e21b5b841@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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).
From: | "Max Zorloff" <zorloff(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Declaring multidimensional arrays in pl/pgsql |
Date: | 2007-11-29 14:28:38 |
Message-ID: | op.t2j450qnll0p5y@1-rtt202dnf3uds.mshome.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, 29 Nov 2007 18:11:22 +0400, Rodrigo De León <rdeleonp(at)gmail(dot)com>
wrote:
> On Nov 29, 2007 3:34 AM, Max Zorloff <zorloff(at)gmail(dot)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.
From: | "Max Zorloff" <zorloff(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Declaring multidimensional arrays in pl/pgsql |
Date: | 2007-11-29 15:31:47 |
Message-ID: | op.t2j729fhll0p5y@1-rtt202dnf3uds.mshome.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, 29 Nov 2007 19:21:03 +0400, Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)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.
From: | "Max Zorloff" <zorloff(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Declaring multidimensional arrays in pl/pgsql |
Date: | 2007-11-29 17:25:53 |
Message-ID: | op.t2kddf1jll0p5y@1-rtt202dnf3uds.mshome.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, 29 Nov 2007 21:15:50 +0400, Rodrigo De León <rdeleonp(at)gmail(dot)com>
wrote:
> On Nov 29, 2007 9:33 AM, Max Zorloff <zorloff(at)gmail(dot)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.