Greetings:

I have a customer history table that contains several integer and decimal
arrays for recording sales and load count information for each of the 12
months. I'm trying to modify one of the arrays in a pre-update trigger. I'm
looping as in:

for x in 1..12 loop
array[x] = <some_value>;

Correction:

That should read: new.array[x] = <some_value>;

---------- Forwarded Message ----------

Subject: [NOVICE] Array Question
Date: Friday 29 October 2004 09:08 pm
From: Terry Lee Tucker <terry (AT) esc1 (DOT) com>
To: pgsql-novice (AT) postgresql (DOT) org

Greetings:

I have a customer history table that contains several integer and decimal
arrays for recording sales and load count information for each of the 12
months. I'm trying to modify one of the arrays in a pre-update trigger. I'm
looping as in:

for x in 1..12 loop
array[x] = <some_value>;

Terry Lee Tucker <terry (AT) esc1 (DOT) com> writes:
plpgsql can do this since 7.4. What PG version are you running?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Sorry, my brain was fried last night. Here is the version:

PostgreSQL 7.3.6-RH on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-39)

So, looks like I'm a little short on version. How do I handle with 7.3?

On Friday 29 October 2004 10:07 pm, Tom Lane saith:
--

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

The documentation reads as follows:
"An array value can be replaced completely:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';

or updated at a single element:

UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';

or updated in a slice:

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol'; "

I try this simple statement and get the same error:

UPDATE chist SET broker[10] = 195 WHERE code = 'AAATMOUS000';

Any ideas?
Thanks...

On Friday 29 October 2004 10:07 pm, Tom Lane saith:
--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Nevermind, the error was coming from the trigger. If I comment out the line in
the trigger, the second option below, "or updated at a single element", works
from the psql interface. It just doesn't work that way in the trigger code
(

On Saturday 30 October 2004 05:35 am, Terry Lee Tucker saith:
--
Quote: 60
"Power tempts even the best of men to take liberties with the truth."

--Joseph Sobran

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Terry Lee Tucker <terry (AT) esc1 (DOT) com> writes:
You update to 7.4. plpgsql couldn't handle this before.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org