Обсуждение: Strugging with NEW and OLD records.

I am a newbie to the stored proc. game and this is eating my brain.

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

> 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

What am I doing wrong?

ScriptHead

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

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@archonet.com> wrote in message
news:43845DF5.4020401@archonet.com...
> 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
>