Postgres Pro
Downloads
Home   >   mailing lists

Thread: Declaring empty, non-NULL array in plpgsql

Declaring empty, non-NULL array in plpgsql

From
CSN
Date:
Is there a way to declare an array in plpgsql so that
it's empty (not NULL)? The following causes arr to end
up being NULL:

arr varchar[];
-- do stuff with arr..
arr = array_append(arr, '','');


And this causes an unwanted element at the front:

arr varchar[] := array[''''];
-- do stuff with arr..
arr = array_append(arr, '','');


__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

Re: Declaring empty, non-NULL array in plpgsql

From
CSN
Date:
This avoids the NULL problem - is there a better way?

    aCats varchar[] = array[''''];
    vCats varchar;
    iIndex int := 1;
    rItem record;

    ...

    for rItem in select ... loop
        aCats[iIndex] := rItem.name;

        iIndex := iIndex + 1;
    end loop;

    vCats := array_to_string(aCats, '','');
    ...



--- CSN <cool_screen_name90001@yahoo.com> wrote:
> Is there a way to declare an array in plpgsql so
> that
> it's empty (not NULL)? The following causes arr to
> end
> up being NULL:
>
> arr varchar[];
> -- do stuff with arr..
> arr = array_append(arr, '','');
>
>
> And this causes an unwanted element at the front:
>
> arr varchar[] := array[''''];
> -- do stuff with arr..
> arr = array_append(arr, '','');
>
>
> __________________________________
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now
> http://companion.yahoo.com/
>


__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

Re: Declaring empty, non-NULL array in plpgsql

From
Joe Conway
Date:
CSN wrote:
> This avoids the NULL problem - is there a better way?
>
>     aCats varchar[] = array[''''];

aCats varchar[] = ''{}'';

This gives you a truly empty array that can later become single or
multidimensional.

e.g.

regression=# select '{}'::int[] || 1;
  ?column?
----------
  {1}
(1 row)

regression=# select '{}'::int[] || array[1,2];
  ?column?
----------
  {1,2}
(1 row)

regression=# select '{}'::int[] || array[[1,2],[3,4]];
    ?column?
---------------
  {{1,2},{3,4}}
(1 row)

HTH,

Joe

Re: Declaring empty, non-NULL array in plpgsql

From
Tom Lane
Date:
CSN <cool_screen_name90001@yahoo.com> writes:
> Is there a way to declare an array in plpgsql so that
> it's empty (not NULL)?

Sure.  An empty-array literal is '{}', so:

regression=# create function foo(int) returns varchar[] as '
regression'# declare
regression'#   arr varchar[] := ''{}'';
regression'# begin
regression'#   for i in 1..$1 loop
regression'#     arr = array_append(arr, i::varchar);
regression'#   end loop;
regression'# return arr;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo(10);
          foo
------------------------
 {1,2,3,4,5,6,7,8,9,10}
(1 row)


            regards, tom lane