pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedError when using array variable
6+ messages / 4 participants
[nested] [flat]
* Error when using array variable
@ 2005-07-21 09:29 Dongsoo Yoon <dsyoon@metasoftworks.com>
0 siblings, 0 replies; 6+ messages in thread
From: Dongsoo Yoon @ 2005-07-21 09:29 UTC (permalink / raw)
To: pgsql-sql
I use a function using array variable as following.
The function returns array type.
When array variable is allocated with any value,
allocating is not done.
I can't find why it is.
-------------------------------------------------------
CREATE OR REPLACE FUNCTION arr_test()
RETURNS NUMERIC[] AS $BODY$
DECLARE
v_count NUMERIC default 0;
v_dayIndex NUMERIC default 0;
t_modifiedTimes NUMERIC[];
v_testval NUMERIC default 0;
....
BEGIN
....
for v_count in 1..5 loop
v_dayIndex := v_dayIndex + 1;
t_modifiedTimes[v_dayIndex ] := v_count;
v_testval := t_modifiedTimes[v_dayIndex ] ; -------------->¹è¿¿¡ ÀúÀåµÈ °ª ÀÓ½ÃÀúÀå
raise exception 'ÀÓÀÇ¿¡·¯»ý¼º t_modifiedTimes[v_dayIndex ]=', v_testval ;
-------------->°Á¦ÀûÀ¸·Î exception ¹ß»ý½ÃÅ´
end loop;
....
return t_modifiedTimes;
END;
$BODY$ LANGUAGE plpgsql;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Error when using array variable
@ 2005-07-22 09:24 Dongsoo Yoon <dsyoon@metasoftworks.com>
2005-07-22 09:37 ` Re: Error when using array variable Gnanavel S <s.gnanavel@gmail.com>
2005-07-22 13:51 ` Re: Error when using array variable Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 6+ messages in thread
From: Dongsoo Yoon @ 2005-07-22 09:24 UTC (permalink / raw)
To: pgsql-sql
I use a function using array variable as following.
The function returns array type.
When array variable is allocated with any value,
allocating is not done.
I can't find why it is.
-------------------------------------------------------
CREATE OR REPLACE FUNCTION arr_test()
RETURNS NUMERIC[] AS $BODY$
DECLARE
v_count NUMERIC default 0;
v_dayIndex NUMERIC default 0;
t_modifiedTimes NUMERIC[];
v_testval NUMERIC default 0;
....
BEGIN
....
for v_count in 1..5 loop
v_dayIndex := v_dayIndex + 1;
t_modifiedTimes[v_dayIndex ] := v_count;
v_testval := t_modifiedTimes[v_dayIndex ] ; -------------->¹è¿¿¡ ÀúÀåµÈ °ª ÀÓ½ÃÀúÀå
raise exception 'ÀÓÀÇ¿¡·¯»ý¼º t_modifiedTimes[v_dayIndex ]=', v_testval ;
-------------->°Á¦ÀûÀ¸·Î exception ¹ß»ý½ÃÅ´
end loop;
....
return t_modifiedTimes;
END;
$BODY$ LANGUAGE plpgsql;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error when using array variable
2005-07-22 09:24 Error when using array variable Dongsoo Yoon <dsyoon@metasoftworks.com>
@ 2005-07-22 09:37 ` Gnanavel S <s.gnanavel@gmail.com>
2005-07-22 23:38 ` Re: Error when using array variable Michael Fuhr <mike@fuhr.org>
1 sibling, 1 reply; 6+ messages in thread
From: Gnanavel S @ 2005-07-22 09:37 UTC (permalink / raw)
To: Dongsoo Yoon <dsyoon@metasoftworks.com>; +Cc: pgsql-sql
On 7/22/05, Dongsoo Yoon <dsyoon@metasoftworks.com> wrote:
>
>
> I use a function using array variable as following.
> The function returns array type.
> When array variable is allocated with any value,
> allocating is not done.
> I can't find why it is.
> -------------------------------------------------------
> CREATE OR REPLACE FUNCTION arr_test()
> RETURNS NUMERIC[] AS $BODY$
> DECLARE
> v_count NUMERIC default 0;
> v_dayIndex NUMERIC default 0;
> t_modifiedTimes NUMERIC[];
> v_testval NUMERIC default 0;
> ....
> BEGIN
> ....
> for v_count in 1..5 loop
> v_dayIndex := v_dayIndex + 1;
>
t_modifiedTimes[v_dayIndex ] := v_count;
>
You need to initialse the array, Otherwise any value added to the array will
be null.
v_testval := t_modifiedTimes[v_dayIndex ] ; -------------->¹è¿¿¡ ÀúÀåµÈ °ª ÀÓ½ÃÀúÀå
> raise exception 'ÀÓÀÇ¿¡·¯»ý¼º t_modifiedTimes[v_dayIndex ]=', v_testval ;
> -------------->°Á¦ÀûÀ¸·Î exception ¹ß»ý½ÃÅ´
> end loop;
> ....
> return t_modifiedTimes;
> END;
> $BODY$ LANGUAGE plpgsql;
>
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error when using array variable
2005-07-22 09:24 Error when using array variable Dongsoo Yoon <dsyoon@metasoftworks.com>
2005-07-22 09:37 ` Re: Error when using array variable Gnanavel S <s.gnanavel@gmail.com>
@ 2005-07-22 23:38 ` Michael Fuhr <mike@fuhr.org>
2005-07-23 04:56 ` Re: Error when using array variable Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-07-22 23:38 UTC (permalink / raw)
To: Gnanavel S <s.gnanavel@gmail.com>; +Cc: Dongsoo Yoon <dsyoon@metasoftworks.com>; pgsql-sql
On Fri, Jul 22, 2005 at 03:07:04PM +0530, Gnanavel S wrote:
> On 7/22/05, Dongsoo Yoon <dsyoon@metasoftworks.com> wrote:
> > CREATE OR REPLACE FUNCTION arr_test()
> > RETURNS NUMERIC[] AS $BODY$
> > DECLARE
> > v_count NUMERIC default 0;
> > v_dayIndex NUMERIC default 0;
> > t_modifiedTimes NUMERIC[];
> > v_testval NUMERIC default 0;
> > ....
> > BEGIN
> > ....
> > for v_count in 1..5 loop
> > v_dayIndex := v_dayIndex + 1;
> >
> t_modifiedTimes[v_dayIndex ] := v_count;
>
> You need to initialse the array, Otherwise any value added to the array will
> be null.
This isn't necessary in 8.0, which we can infer is being used because
of the dollar quotes. See the 8.0 Release Notes:
http://www.postgresql.org/docs/8.0/static/release-8-0.html
"Updating an element or slice of a NULL array value now produces a
non-NULL array result, namely an array containing just the assigned-to
positions."
I don't recall if early 8.0 versions had any bugs in this respect,
but it does indeed work in 8.0.3.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error when using array variable
2005-07-22 09:24 Error when using array variable Dongsoo Yoon <dsyoon@metasoftworks.com>
2005-07-22 09:37 ` Re: Error when using array variable Gnanavel S <s.gnanavel@gmail.com>
2005-07-22 23:38 ` Re: Error when using array variable Michael Fuhr <mike@fuhr.org>
@ 2005-07-23 04:56 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-07-23 04:56 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Gnanavel S <s.gnanavel@gmail.com>; Dongsoo Yoon <dsyoon@metasoftworks.com>; pgsql-sql
Michael Fuhr <mike@fuhr.org> writes:
> On Fri, Jul 22, 2005 at 03:07:04PM +0530, Gnanavel S wrote:
>> You need to initialse the array, Otherwise any value added to the array will
>> be null.
> This isn't necessary in 8.0, which we can infer is being used because
> of the dollar quotes.
However, plpgsql wasn't fixed to follow that behavior till 8.0.2 or so.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Error when using array variable
2005-07-22 09:24 Error when using array variable Dongsoo Yoon <dsyoon@metasoftworks.com>
@ 2005-07-22 13:51 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-07-22 13:51 UTC (permalink / raw)
To: Dongsoo Yoon <dsyoon@metasoftworks.com>; +Cc: pgsql-sql
On Fri, Jul 22, 2005 at 06:24:03PM +0900, Dongsoo Yoon wrote:
>
> When array variable is allocated with any value,
> allocating is not done.
It's not clear what this means -- what are you expecting to happen,
and what actually is happening?
> v_testval := t_modifiedTimes[v_dayIndex ] ; -------------->?????? ?????? ?? ????????
>
> raise exception '???????????? t_modifiedTimes[v_dayIndex ]=', v_testval ;
Is this RAISE just for debugging purposes? Are you aware that you
need to include a % character in the format string if you want to
display the subsequent argument's value?
http://www.postgresql.org/docs/8.0/static/plpgsql-errors-and-messages.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-07-23 04:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-21 09:29 Error when using array variable Dongsoo Yoon <dsyoon@metasoftworks.com>
2005-07-22 09:24 Error when using array variable Dongsoo Yoon <dsyoon@metasoftworks.com>
2005-07-22 09:37 ` Gnanavel S <s.gnanavel@gmail.com>
2005-07-22 23:38 ` Michael Fuhr <mike@fuhr.org>
2005-07-23 04:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-22 13:51 ` Michael Fuhr <mike@fuhr.org>
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