Thread: Array_append does not work with Array variables in PL/pgSQL?

Folks,

See if you can spot any mistake I'm making here.   I've declared the following 
array variables in a plpgsql function:
v_vals TEXT[];n_vals TEXT[];

After some manipulation, I try to synch them:

n_vals := array_append(n_vals, v_vals[arrloop]);
val_result := v_vals[arrloop];
RAISE NOTICE ''orig value %'', val_result;
val_result := array_to_string(n_vals, '', '');
RAISE NOTICE ''derived value %'', val_result;

And I get:

NOTICE:  orig value 04/01/2004
NOTICE:  derived value <NULL>
NOTICE:  orig value 04/01/2004
NOTICE:  derived value <NULL>

It seems like I cannot assign new elements to arrays inside a PL/pgsql 
function.   What gives here?  

PostgreSQL 7.4.1 on Linux.

-- 
-Josh BerkusAglio Database SolutionsSan Francisco

Josh Berkus wrote:
>  v_vals TEXT[];
>  n_vals TEXT[];

try:  v_vals TEXT[] := ''{}'';  n_vals TEXT[] := ''{}'';

You have to initialize the array to something non-null, even if that be 
an empty array (note that there is a difference). When trying to append 
an element to a NULL valued array, you wind up with a NULL result. It is 
similar to:

regression=# select (NULL || 'abc') is null; ?column?
---------- t
(1 row)

Joe

Joe,

> You have to initialize the array to something non-null, even if that be 
> an empty array (note that there is a difference). When trying to append 
> an element to a NULL valued array, you wind up with a NULL result. It is 
> similar to:

Aha!

I knew that I was missing something fundamental.

BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL?

-- 
-Josh BerkusAglio Database SolutionsSan Francisco

Josh Berkus wrote:
> BTW, did you get my e-mail to Hackers about ARRAY[] IS NULL?
> 

I saw it, but I've been too swamped to really read it. I'll try to carve 
out some time this afternoon.

Joe

Joe,

> I saw it, but I've been too swamped to really read it. I'll try to carve 
> out some time this afternoon.

No urgency on my part.   More something to fix for 7.5

-- 
-Josh BerkusAglio Database SolutionsSan Francisco