Thread: How to access array elements via PL/pgSQL trigger?
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
Roland Roberts <roland@astrofoto.org> writes:
> 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 "["
Most likely this error is not coming from plpgsql, but from the core
SQL parser spitting up on some transformed query that plpgsql tried
to feed it. It'll be educational to set the debug level to 2 or more
(in a fresh backend) and retry the failing query. That should cause
the postmaster log to accumulate the queries being sent down to the
SQL parser.
My first thought is that plpgsql may not support the syntax
arrayvar[subscript] := something
but I've not tried it.
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> Most likely this error is not coming from plpgsql, but from
Tom> the core SQL parser spitting up on some transformed query
Tom> that plpgsql tried to feed it. It'll be educational to set
Tom> the debug level to 2 or more (in a fresh backend) and retry
Tom> the failing query. That should cause the postmaster log to
Tom> accumulate the queries being sent down to the SQL parser.
I'll try bumping up the debug level tomorrow morning when I'm awake
enough to know what I'm doing....
Tom> My first thought is that plpgsql may not support the syntax
Tom> arrayvar[subscript] := something
Tom> but I've not tried it.
But line 4 (where it *says* the error is located) reads
new.total := new.level[1] + ... ;
(which might be a variation of the same thing).
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
Okay, I've re-executed the query after setting debug_level = 2 and
restarting the postmaster to use a log file. Here is an extract from
the log starting from my connect up to the first aborted insert on the
"exam" table. All of the activity up to that point is correct (i.e.,
the previous duplicate key errors).
DEBUG: connection: host=192.168.2.50 user=roland database=psr
DEBUG: InitPostgres
DEBUG: StartTransactionCommand
DEBUG: query: select getdatabaseencoding()
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: insert into district (number, borough) values (22, 'BKLN')
DEBUG: ProcessQuery
ERROR: Cannot insert a duplicate key into unique index district_pkey
DEBUG: AbortCurrentTransaction
DEBUG: StartTransactionCommand
DEBUG: query: insert into school (number, district, level) values (312, 22, 'PS')
DEBUG: ProcessQuery
ERROR: Cannot insert a duplicate key into unique index uk_school
DEBUG: AbortCurrentTransaction
DEBUG: StartTransactionCommand
DEBUG: query: select id from school where number=312 and district=22 and level='PS'
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: update school set name = 'BERGEN BEACH SCHOOL' where id = 4
DEBUG: ProcessQuery
DEBUG: query: SELECT oid FROM ONLY "school_level_lookup" WHERE "id" = $1 FOR UPDATE OF "school_level_lookup"
DEBUG: query: SELECT oid FROM ONLY "district" WHERE "number" = $1 FOR UPDATE OF "district"
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: insert into exam (type, school_id, year, population, level) values ('ELA', 4, 1999, 'GE', '{ 7,87,208,73
}')
DEBUG: ProcessQuery
NOTICE: plpgsql: ERROR during compile of exam_statistics_fixup near line 4
ERROR: parse error at or near "["
DEBUG: AbortCurrentTransaction
Does this tell you any more? Should I log this as a bug? (The bug
form on the web site doesn't seem to be found right now...).
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
Roland Roberts <roland@astrofoto.org> writes:
> DEBUG: StartTransactionCommand
> DEBUG: query: insert into exam (type, school_id, year, population, level) values ('ELA', 4, 1999, 'GE', '{
7,87,208,73}')
> DEBUG: ProcessQuery
> NOTICE: plpgsql: ERROR during compile of exam_statistics_fixup near line 4
> ERROR: parse error at or near "["
> DEBUG: AbortCurrentTransaction
Okay, so it's not coming from a passed-down query. I think my original
guess is right: plpgsql doesn't support assignment to array elements.
> Should I log this as a bug?
"Missing feature" would be more like it. Postgres' array support is
pretty weak in a lot of places, not only plpgsql.
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Should I log this as a bug?
Tom> "Missing feature" would be more like it. Postgres' array
Tom> support is pretty weak in a lot of places, not only plpgsql.
And time to redesign my tables... :-(
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