pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roland Roberts <roland@astrofoto.org>
To: Postgres SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Working with array value columns in PL/pgSQL triggers/functions
Date: 27 Dec 2001 16:14:23 -0500
Message-ID: <m2zo44bc28.fsf@tycho.rlent.pnet> (raw)

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



Message-ID: <m2zo44bc28.fsf@tycho.rlent.pnet>
Permalink:  ../m2zo44bc28.fsf@tycho.rlent.pnet/
Also on:    postgresql.org/message-id/m2zo44bc28.fsf@tycho.rlent.pnet

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: roland@astrofoto.org
  Subject: Re: Working with array value columns in PL/pgSQL triggers/functions
  In-Reply-To: <m2zo44bc28.fsf@tycho.rlent.pnet>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox