Re: TRIGGER BEFORE INSERT

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:

> 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();

Did you want a statement level trigger here?

Try adding for each row' to your create trigger statement above.

HTH

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>

--
-------------------------------------------------------------------------------
Jerry Sievers   305 854-3001 (home)     Production Database Administrator
                305 321-1144 (mobil    WWW E-Commerce Consultant