How to access array elements via PL/pgSQL trigger? - Mailing list pgsql-general

I have a (partial) schema as shown below.  When I attempt to insert a
row from a Tcl script, I get the following error from the script:

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 ('ELA', 4, 1999, 'GE', '{ 7,87,208,73 }')

This is running with PostgreSQL 7.1.3 on both client and server.

It looks like I don't understand something about PL/pgSQL functions
and arrays, but I can't find any examples in the documentation or
anything in the TODO list that would indicate there should be a
problem.  So what *am* I missing?

NB: remove the "references" constraints, and you should be able to run
this example in isolation from the rest of my database.

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
    -- Force derived columns to be consistent with new data.
    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