pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedArray_append does not work with Array variables in PL/pgSQL?
5+ messages / 2 participants
[nested] [flat]
* Array_append does not work with Array variables in PL/pgSQL?
@ 2004-04-01 21:20 Josh Berkus <josh@agliodbs.com>
2004-04-01 21:36 ` Re: Array_append does not work with Array variables in PL/pgSQL? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2004-04-01 21:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: mail@joeconway.com
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Array_append does not work with Array variables in PL/pgSQL?
2004-04-01 21:20 Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
@ 2004-04-01 21:36 ` Joe Conway <mail@joeconway.com>
2004-04-01 21:48 ` Re: Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Joe Conway @ 2004-04-01 21:36 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Array_append does not work with Array variables in PL/pgSQL?
2004-04-01 21:20 Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
2004-04-01 21:36 ` Re: Array_append does not work with Array variables in PL/pgSQL? Joe Conway <mail@joeconway.com>
@ 2004-04-01 21:48 ` Josh Berkus <josh@agliodbs.com>
2004-04-01 21:51 ` Re: Array_append does not work with Array variables in PL/pgSQL? Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2004-04-01 21:48 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Array_append does not work with Array variables in PL/pgSQL?
2004-04-01 21:20 Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
2004-04-01 21:36 ` Re: Array_append does not work with Array variables in PL/pgSQL? Joe Conway <mail@joeconway.com>
2004-04-01 21:48 ` Re: Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
@ 2004-04-01 21:51 ` Joe Conway <mail@joeconway.com>
2004-04-01 21:57 ` Re: Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Joe Conway @ 2004-04-01 21:51 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Array_append does not work with Array variables in PL/pgSQL?
2004-04-01 21:20 Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
2004-04-01 21:36 ` Re: Array_append does not work with Array variables in PL/pgSQL? Joe Conway <mail@joeconway.com>
2004-04-01 21:48 ` Re: Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
2004-04-01 21:51 ` Re: Array_append does not work with Array variables in PL/pgSQL? Joe Conway <mail@joeconway.com>
@ 2004-04-01 21:57 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2004-04-01 21:57 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-04-01 21:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-01 21:20 Array_append does not work with Array variables in PL/pgSQL? Josh Berkus <josh@agliodbs.com>
2004-04-01 21:36 ` Joe Conway <mail@joeconway.com>
2004-04-01 21:48 ` Josh Berkus <josh@agliodbs.com>
2004-04-01 21:51 ` Joe Conway <mail@joeconway.com>
2004-04-01 21:57 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox