Thread: 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
> 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 >