pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with array subscripts in plpgsql trigger function
5+ messages / 2 participants
[nested] [flat]
* Problem with array subscripts in plpgsql trigger function
@ 2006-07-05 16:52 Erik Jones <erik@myemma.com>
2006-07-05 17:36 ` Re: Problem with array subscripts in plpgsql trigger function Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 5+ messages in thread
From: Erik Jones @ 2006-07-05 16:52 UTC (permalink / raw)
To: pgsql-sql
Ok, I have a trigger set up on the following (stripped down) table:
CREATE TABLE members (
member_id bigint,
member_status_id smallint,
member_is_deleted boolean
);
Here's a shortened version of the trigger function:
CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$
DECLARE
status_deltas integer[];
BEGIN
IF(NEW.member_status_id != OLD.member_status_id AND
NEW.member_is_deleted IS NOT TRUE) THEN
status_deltas[NEW.member_status_id] := 1;
status_deltas[OLD.member_status_id] := -1;
END IF;
/*and after a couple more such conditional assignments I use the
values in status_deltas to update another table holding status totals here*/
END;
$um$ LANGUAGE plpgsql;
on the two lines that access set array values I'm getting the following
error:
ERROR: invalid array subscripts
What gives?
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with array subscripts in plpgsql trigger function
2006-07-05 16:52 Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
@ 2006-07-05 17:36 ` Aaron Bono <postgresql@aranya.com>
2006-07-05 17:42 ` Re: Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 5+ messages in thread
From: Aaron Bono @ 2006-07-05 17:36 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
On 7/5/06, Erik Jones <erik@myemma.com> wrote:
>
> Ok, I have a trigger set up on the following (stripped down) table:
>
> CREATE TABLE members (
> member_id bigint,
> member_status_id smallint,
> member_is_deleted boolean
> );
>
> Here's a shortened version of the trigger function:
>
> CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$
> DECLARE
> status_deltas integer[];
> BEGIN
> IF(NEW.member_status_id != OLD.member_status_id AND
> NEW.member_is_deleted IS NOT TRUE) THEN
> status_deltas[NEW.member_status_id] := 1;
> status_deltas[OLD.member_status_id] := -1;
> END IF;
> /*and after a couple more such conditional assignments I use the
> values in status_deltas to update another table holding status totals
> here*/
> END;
> $um$ LANGUAGE plpgsql;
>
> on the two lines that access set array values I'm getting the following
> error:
>
> ERROR: invalid array subscripts
>
> What gives?
What values are being used for member_status_id?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with array subscripts in plpgsql trigger function
2006-07-05 16:52 Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
2006-07-05 17:36 ` Re: Problem with array subscripts in plpgsql trigger function Aaron Bono <postgresql@aranya.com>
@ 2006-07-05 17:42 ` Erik Jones <erik@myemma.com>
2006-07-05 17:56 ` Re: Problem with array subscripts in plpgsql trigger function Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 5+ messages in thread
From: Erik Jones @ 2006-07-05 17:42 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
Aaron Bono wrote:
> On 7/5/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.com>> wrote:
>
> Ok, I have a trigger set up on the following (stripped down) table:
>
> CREATE TABLE members (
> member_id bigint,
> member_status_id smallint,
> member_is_deleted boolean
> );
>
> Here's a shortened version of the trigger function:
>
> CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$
> DECLARE
> status_deltas integer[];
> BEGIN
> IF(NEW.member_status_id != OLD.member_status_id AND
> NEW.member_is_deleted IS NOT TRUE) THEN
> status_deltas[NEW.member_status_id] := 1;
> status_deltas[OLD.member_status_id] := -1;
> END IF;
> /*and after a couple more such conditional assignments I use the
> values in status_deltas to update another table holding status
> totals here*/
> END;
> $um$ LANGUAGE plpgsql;
>
> on the two lines that access set array values I'm getting the
> following
> error:
>
> ERROR: invalid array subscripts
>
> What gives?
>
>
>
> What values are being used for member_status_id?
>
1, 2, and 3
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with array subscripts in plpgsql trigger function
2006-07-05 16:52 Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
2006-07-05 17:36 ` Re: Problem with array subscripts in plpgsql trigger function Aaron Bono <postgresql@aranya.com>
2006-07-05 17:42 ` Re: Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
@ 2006-07-05 17:56 ` Aaron Bono <postgresql@aranya.com>
2006-07-05 18:10 ` Re: Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 5+ messages in thread
From: Aaron Bono @ 2006-07-05 17:56 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
On 7/5/06, Erik Jones <erik@myemma.com> wrote:
>
> Aaron Bono wrote:
> > On 7/5/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.com>>
> wrote:
> >
> > Ok, I have a trigger set up on the following (stripped down) table:
> >
> > CREATE TABLE members (
> > member_id bigint,
> > member_status_id smallint,
> > member_is_deleted boolean
> > );
> >
> > Here's a shortened version of the trigger function:
> >
> > CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$
> > DECLARE
> > status_deltas integer[];
> > BEGIN
> > IF(NEW.member_status_id != OLD.member_status_id AND
> > NEW.member_is_deleted IS NOT TRUE) THEN
> > status_deltas[NEW.member_status_id] := 1;
> > status_deltas[OLD.member_status_id] := -1;
> > END IF;
> > /*and after a couple more such conditional assignments I use the
> > values in status_deltas to update another table holding status
> > totals here*/
> > END;
> > $um$ LANGUAGE plpgsql;
> >
> > on the two lines that access set array values I'm getting the
> > following
> > error:
> >
> > ERROR: invalid array subscripts
> >
> > What gives?
> >
> >
> >
> > What values are being used for member_status_id?
> >
> 1, 2, and 3
I did some digging through the documentation and cannot find any examples of
using arrays like this. Do you have to initialize the array before you use
it?
Does anyone know where to look for informaiton about using arrays in stored
procedures?
-Aaron
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with array subscripts in plpgsql trigger function
2006-07-05 16:52 Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
2006-07-05 17:36 ` Re: Problem with array subscripts in plpgsql trigger function Aaron Bono <postgresql@aranya.com>
2006-07-05 17:42 ` Re: Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
2006-07-05 17:56 ` Re: Problem with array subscripts in plpgsql trigger function Aaron Bono <postgresql@aranya.com>
@ 2006-07-05 18:10 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 5+ messages in thread
From: Erik Jones @ 2006-07-05 18:10 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
Aaron Bono wrote:
> On 7/5/06, *Erik Jones* <erik@myemma.com <mailto:erik@myemma.com>> wrote:
>
> Aaron Bono wrote:
> > On 7/5/06, *Erik Jones* <erik@myemma.com
> <mailto:erik@myemma.com> <mailto:erik@myemma.com
> <mailto:erik@myemma.com>>> wrote:
> >
> > Ok, I have a trigger set up on the following (stripped down)
> table:
> >
> > CREATE TABLE members (
> > member_id bigint,
> > member_status_id smallint,
> > member_is_deleted boolean
> > );
> >
> > Here's a shortened version of the trigger function:
> >
> > CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER
> AS $um$
> > DECLARE
> > status_deltas integer[];
> > BEGIN
> > IF(NEW.member_status_id != OLD.member_status_id AND
> > NEW.member_is_deleted IS NOT TRUE) THEN
> > status_deltas[NEW.member_status_id] := 1;
> > status_deltas[OLD.member_status_id] := -1;
> > END IF;
> > /*and after a couple more such conditional assignments I
> use the
> > values in status_deltas to update another table holding status
> > totals here*/
> > END;
> > $um$ LANGUAGE plpgsql;
> >
> > on the two lines that access set array values I'm getting the
> > following
> > error:
> >
> > ERROR: invalid array subscripts
> >
> > What gives?
> >
> >
> >
> > What values are being used for member_status_id?
> >
> 1, 2, and 3
>
>
> I did some digging through the documentation and cannot find any
> examples of using arrays like this. Do you have to initialize the
> array before you use it?
>
> Does anyone know where to look for informaiton about using arrays in
> stored procedures?
>
> -Aaron
>
Ok, that was apparently it. I found <a
href="http://archives.postgresql.org/pgsql-general/2005-02/msg01270.php">this</a;
thread in the archive which indicated to me that without first
initializing the array the bounds weren't set and thus a subscript error
if each new index isn't sequential.
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-05 18:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-05 16:52 Problem with array subscripts in plpgsql trigger function Erik Jones <erik@myemma.com>
2006-07-05 17:36 ` Aaron Bono <postgresql@aranya.com>
2006-07-05 17:42 ` Erik Jones <erik@myemma.com>
2006-07-05 17:56 ` Aaron Bono <postgresql@aranya.com>
2006-07-05 18:10 ` Erik Jones <erik@myemma.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