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
