Hi All!
I have some old piece of code, that worked two years ago (Postgres
version 7.2, I think), but doesn't work within Postgres 8.1.4 now.
The story is, that I have a trigger on a table (business day
statistics), that is fired before insert; it updates another table
(detailed transaction log), and saves statistics from that update within
the freshly inserted record.
Cutting down much larger (and obfuscated) schema to its critical
section, I've came with the following snippet:
----------------------------------------------------------------
CREATE TABLE test_days (id serial unique, dnia date not null default
current_date-'1day'::interval, total int not null);
CREATE TABLE test_utarg(tm timestamp not null, nic int, amount int not
null, dnia int references test_days(id));
INSERT INTO test_utarg (tm,nic, amount) SELECT current_timestamp -
interval_mul('1min'::interval, (random()*10000)::integer),
generate_series(1,88), (random()*10000)::integer;
CREATE FUNCTION prado() RETURNS trigger AS $$ DECLARE wydano INTEGER;
BEGIN UPDATE test_utarg SET dnia=new.id WHERE tm BETWEEN new.dnia AND
new.dnia+'1day'::interval; GET DIAGNOSTICS wydano := ROW_COUNT;
new.total := wydano; RETURN new; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER mocarny BEFORE INSERT ON test_days EXECUTE PROCEDURE
prado();
INSERT INTO test_days (dnia) VALUES ('2007-01-06');
ERROR: record "new" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is
indeterminate.
CONTEXT: PL/pgSQL function "prado" line 1 at SQL statement
------------------------------------------------------------
And to my ultimate surprise, this one breaks with yet another ERROR.
In the original schema, the ERROR was abount TEST_UTARG.DNIA referencing
a "not yet available" NEW.ID. ... as if constraints within transactions
(inside trigger) were checked on each step, and not at the end of
transaction .... as it looks was the case of postgres v7.2.
But the ERROR quoted abobe warries me even more. Is it true, that NEW is
really "not-yet-assigned" in BEFORE INSERT trigger?? Or may be I'm not
seeing some other obvious mistake I've done in the code above?
Help, pls!
--
-R