Declaring empty, non-NULL array in plpgsql

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/

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

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