Re: Strugging with NEW and OLD records.

Script Head wrote:
> I am a newbie to the stored proc. game and this is eating my brain.

The error message is accurate but not useful...

>>CREATE TABLE name(first VARCHAR(32) NULL,last VARCHAR(32) NULL, extra
> VARCHAR(32) NULL );
>
>>CREATE OR REPLACE FUNCTION update_name() RETURNS opaque AS '
> DECLARE
> BEGIN
> NEW.extra:=NEW.first;
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
>
>>CREATE TRIGGER update_name_extra BEFORE INSERT
> ON name
> EXECUTE PROCEDURE update_name();

The CREATE TRIGGER is the problem...

>>INSERT INTO name(first,last) VALUES('script','head');
>
> ERROR: record "new" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
> CONTEXT: PL/pgSQL function "update_name" line 4 at assignment

You need to add "FOR EACH ROW" before "EXECUTE PROCEDURE" otherwise you
have a statement-level trigger which doesn't give you access to NEW/OLD.

HTH
--
Richard Huxton
Archonet Ltd