Thread: error in docs

Hi all,
    I'm in trouble with examples in
    http://www.postgresql.org/docs/7.3/interactive/arrays.html.
    Step by step:

-------------cut here-----------
gelma@cogno:~$ createdb test
CREATE DATABASE
gelma@cogno:~$ psql test
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# CREATE TABLE sal_emp (
test(#      name            text,
test(#      pay_by_quarter  integer[],
test(#      schedule        text[][]
test(#  );
CREATE TABLE
test=# INSERT INTO sal_emp
test-#      VALUES ('Bill',
test(#      '{10000, 10000, 10000, 10000}',
test(#      '{{"meeting", "lunch"}, {}}');
INSERT 44628 1
test=#
test=#  INSERT INTO sal_emp
test-#      VALUES ('Carol',
test(#      '{20000, 25000, 25000, 25000}',
test(#      '{{"talk", "consult"}, {"meeting"}}');
INSERT 44629 1
test=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <>
pay_by_quarter[2];
 name
-------
 Carol
(1 row)

test=# SELECT pay_by_quarter[3] FROM sal_emp;
 pay_by_quarter
----------------
          10000
          25000
(2 rows)
test=# SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
     schedule
------------------
 {{meeting},{""}}
(1 row)

test=# SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
     schedule
------------------
 {{meeting},{""}}
(1 row)

test=# UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
test-#      WHERE name = 'Carol';
UPDATE 1
test=# UPDATE sal_emp SET pay_by_quarter[4] = 15000
test-#      WHERE name = 'Bill';
UPDATE 1
test=# UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
test-#      WHERE name = 'Carol';
UPDATE 1
test=#
test=# CREATE TABLE tictactoe (
test(#      squares   integer[3][3]
test(#  );
CREATE TABLE
test=#
test=# SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
 array_dims
------------
 [1:2][1:1]
(1 row)

test=# SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
test-#                              pay_by_quarter[2] = 10000 OR
test-#                              pay_by_quarter[3] = 10000 OR
test-#                              pay_by_quarter[4] = 10000;
 name |      pay_by_quarter       |     schedule
------+---------------------------+------------------
 Bill | {10000,10000,10000,15000} | {{meeting},{""}}
(1 row)

test=# SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;
ERROR:  Unable to identify an operator '*=' for types 'integer[]' and 'integer'
    You will have to retype this query using an explicit cast
---------------cut here--------------

Well, Debian Sid, with these packages installed:
ii  postgresql                  7.3.3-1 Object-relational SQL database, descended from POSTGRES
ii  postgresql-client           7.3.3-1 Front-end programs for PostgreSQL
ii  postgresql-dev              7.3.3-1 Header files for libpq (postgresql library)
ii  postgresql-doc              7.3.3-1 Documentation for the PostgreSQL database

Thanks a lot for your work,
Andrea Gelmini

On Sun, 17 Aug 2003, andrea gelmini wrote:

> test=# SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;
> ERROR:  Unable to identify an operator '*=' for types 'integer[]' and 'integer'
>     You will have to retype this query using an explicit cast

I see from those docs:

However, this quickly becomes tedious for large arrays, and is not helpful
if the size of the array is unknown. Although it is not part of the
primary PostgreSQL distribution, there is an extension available that
defines new functions and operators for iterating over array values.
Using this, the above query could be:

SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;

---
 This is talking about an extension that's not part of the primary
distribution as described in the paragraph before the example.
I believe it's refering to contrib/array.

andrea gelmini wrote:
> test=# SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;
> ERROR:  Unable to identify an operator '*=' for types 'integer[]' and 'integer'
>     You will have to retype this query using an explicit cast

There's nothing wrong with the docs (well, at least not with respect to
your specific problem), you just need to read them again. Here's a quote
from the link you supplied:

"However, this quickly becomes tedious for large arrays, and is not
helpful if the size of the array is unknown. Although it is not part
of the primary PostgreSQL distribution, there is an extension available
that defines new functions and operators for iterating over array
values. Using this, the above query could be:"
<...snip...>
"To install this optional module, look in the contrib/array directory
of the PostgreSQL source distribution."

I have no idea how to install contrib/array using debian's package
manager, but that's what you need to do.

HTH,

Joe