Thread: Array Question

Array Question

From

Terry Lee Tucker

Date:

30 October 2004, 05:12:42

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>;
    .
    .
    .
end loop;

This will not compile. I get the following error:

ERROR:  syntax error at or near "["

If I comment that line out, all works well. Any ideas?

Thanks...

--
Quote: 73
"Inasmuch as these people draw near with their mouths
 And honor Me with their lips,
 But have removed their hearts far from Me,
 And their fear toward Me is taught by the commandment of men..."

 -- Isiah 29:13

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Re: Array Question

From

Tom Lane

Date:

30 October 2004, 06:07:26

Terry Lee Tucker <terry@esc1.com> writes:
>     array[x] = <some_value>;

> This will not compile. I get the following error:
> ERROR:  syntax error at or near "["

plpgsql can do this since 7.4.  What PG version are you running?

            regards, tom lane

Re: Array Question

From

Terry Lee Tucker

Date:

30 October 2004, 13:24:30

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:
> Terry Lee Tucker <terry@esc1.com> writes:
> >     array[x] = <some_value>;
> >
> > This will not compile. I get the following error:
> > ERROR:  syntax error at or near "["
>
> 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@postgresql.org)

--

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Re: Array Question

From

Terry Lee Tucker

Date:

30 October 2004, 13:39:44

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:
> Terry Lee Tucker <terry@esc1.com> writes:
> >     array[x] = <some_value>;
> >
> > This will not compile. I get the following error:
> > ERROR:  syntax error at or near "["
>
> 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@postgresql.org)

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Re: Array Question

From

Terry Lee Tucker

Date:

30 October 2004, 13:47:04

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
:o(

On Saturday 30 October 2004 05:35 am, Terry Lee Tucker saith:
> 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:
> > Terry Lee Tucker <terry@esc1.com> writes:
> > >     array[x] = <some_value>;
> > >
> > > This will not compile. I get the following error:
> > > ERROR:  syntax error at or near "["
> >
> > 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@postgresql.org)
>
> --
>  Work: 1-336-372-6812
>  Cell: 1-336-363-4719
> email: terry@esc1.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
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@esc1.com

Re: Array Question

From

Tom Lane

Date:

30 October 2004, 20:59:53

Terry Lee Tucker <terry@esc1.com> writes:
> PostgreSQL 7.3.6-RH on i386-redhat-linux-gnu, compiled by GCC

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

You update to 7.4.  plpgsql couldn't handle this before.

            regards, tom lane