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
