Re: Strugging with NEW and OLD records.

On top of that... "name" is a postgreSQL internal data type --
essentially -- you need to treat it as if it is an SQL "reserved word"....
Therefore::I strongly recommend that you name your table something other
than "name"... for the same reasons you wouldn't name a table "select" (the
example being try and "select * from select").

As a general rule, you should not use SQL RESERVED words, RDBMS internals,
or DATA TYPE names as table or column identifiers....

HTH...

Greg...

"Richard Huxton" <dev(at)archonet(dot)com> wrote in message
news:43845DF5(dot)4020401(at)archonet(dot)com(dot)(dot)(dot)
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>