Working with array value columns in PL/pgSQL triggers/functions

I have the following (partial) schema for keeping track of exam
statistics on school performance.  The "raw" numbers for the exams are
the number of students falling into each performance level.  The
derived values are (1) the total number of students and (2) the
percentage falling into each level.  I figured I would just keep track
of the raw numbers and create a trigger to modify the derived values
whenever a row is updated.  However, when I attempt to insert a row, I
get the following:

NOTICE:  plpgsql: ERROR during compile of exam_statistics_fixup near line 4
ERROR:  parse error at or near "["       insert into exam (type, school_id, year, population, level) values ('MTH', 3,
1999,'GE', '{ 102,158,117,45 }')
 

(the Tcl code catches the error and displays the SQL statement which
caused the error).

It seems to me that I'm missing something obvious about manipulating
array valued columns in PL/pgSQL.  (FWIW, if you remove the
'references' part, you should be able to run this particular example
in isolation without the rest of the schema.)


create table exam (   type        varchar(4) references exam_type_lookup (type),   school_id   int references
school(id),  year        numeric(4) not null,   population  varchar(3) not null,   total       numeric(4),   level
numeric(4)[],   levelpct    numeric(5,2)[]
 
);

create unique index uk_exam on exam (school_id, year, type, population);
create index idx_exam_1 on exam (school_id);

comment on column exam.type  is 'Type of exam';
comment on column exam.year  is 'Year in which the exam was performed';
comment on column exam.level is 'Number of students performing at the particular level';
comment on column exam.population is 'SE - Special Education, GE - General';

create function exam_statistics_fixup () returns opaque AS '   begin       -- Check that empname and salary are given
   new.total = new.level[1] + new.level[2] + new.level[3] + new.level[4];       new.levelpct[1] = 100 * new.level[1] /
new.total;      new.levelpct[2] = 100 * new.level[2] / new.total;       new.levelpct[3] = 100 * new.level[3] /
new.total;      new.levelpct[4] = 100 * new.level[4] / new.total;       return new;   end;
 
' language 'plpgsql';

create trigger exam_biur 
before insert or update   on exam  for each row execute procedure exam_statistics_fixup();


roland
--            PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375