The following bug has been logged online:

Bug reference: 1370
Logged by: David Bowen
Email address: dmb0317 (AT) frontiernet (DOT) net
PostgreSQL version: 8.0
Operating system: Fedora Core 2/Athlon 950 processor
Description: Problem with arrays in PL/PGSQL
Details:

PostgreSQL 8.0.0rc2

test=# \d scores
Table "public.scores"
Column | Type | Modifiers
---------------+---------+-----------
contestant_id | integer |
category | integer |
score | integer |

test=# select * from scores;
contestant_id | category | score
---------------+----------+-------
1 | 1 | 70
1 | 2 | 73
1 | 3 | 69
2 | 1 | 72
2 | 2 | 70
2 | 3 | 73
3 | 1 | 66
3 | 2 | 75
3 | 3 | 72
(9 rows)

test=# \i /home1/dmb/crosstab
CREATE FUNCTION
test=# select crosstab(1);
ERROR: array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function "crosstab" line 5 at block variables
initialization
$ cat /home1/dmb/crosstab
CREATE OR REPLACE FUNCTION crosstab (integer) RETURNS integer[] AS '
DECLARE
contestant ALIAS FOR $1;
row scores%ROWTYPE;
s integer[3]:= (-1,-1,-1);
BEGIN
FOR row IN SELECT * FROM scores where contestant_id = contestant LOOP
s[row.category] := row.score;
END LOOP;
RETURN s;
END;
' LANGUAGE 'PLPGSQL';

Changing
s integer[3]:= (-1,-1,-1);
to
s integer[3]:= {-1,-1,-1};

test=# \i /home1/dmb/crosstab
CREATE FUNCTION
test=# select crosstab(1);
ERROR: syntax error at or near "{" at character 9
QUERY: SELECT {-1,-1,-1}
CONTEXT: PL/pgSQL function "crosstab" line 5 at block variables
initialization
LINE 1: SELECT {-1,-1,-1}
^
Changing to
s integer[3];
test=# \i /home1/dmb/crosstab
CREATE FUNCTION
test=# select crosstab(1);
crosstab
----------

(1 row)

gdb shows exec_assign_value returning at line 3137 because oldarrayisnull is
TRUE, so s never gets assigned. If you need additional information, ask and
ye shall receive.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Mon, Jan 03, 2005 at 03:11:05AM +0000, David Bowen wrote:

See "Array Value Input" in the "Arrays" section of the "Data Types"
chapter in the documentation; see also "Array Constructors" in the
"Value Expressions" section of the "SQL Syntax" chapter. Either
of the following should work:

s integer[3]:= ARRAY[-1,-1,-1];
s integer[3]:= ''{-1,-1,-1}'';

Since you're running 8.0, consider using dollar quotes around the
function body if backward compatibility isn't necessary -- that way
you don't have to escape single quotes that are inside the function.
See "Dollar-Quoted String Constants" in the "SQL Syntax" chapter.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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