The following bug has been logged online:
Bug reference: 1370
Logged by: David Bowen
Email address: [EMAIL PROTECTED]
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
