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:[EMAIL PROTECTED] > 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 > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
