ERROR: NEW used in non-rule query

From: "Rod Kreisler" <rod(at)23net(dot)net>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: ERROR: NEW used in non-rule query
Date: 2003-01-12 04:27:08
Message-ID: JNEGKNDJGBKLBDGPOPFOKELEDGAA.rod@23net.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

ERROR: NEW used in non-rule query

OK, the above is the error I'm getting upon insert into my table
"prospectNotes". I've included the table schema and trigger/function
declarations. I have no idea why this is happening. I haven't had problems
like this with any of the other tables I have created with other trigger
procedures. I'm sure I have a syntactical or logical error in
v_i_prospectNotes but I sure can't see it. I'd be grateful if someone with
fresh eyes could take a look.

Thanks

Rod

CREATE table "prospectNotes"(
"prospectNoteID" serial NOT NULL CONSTRAINT "PK_prospectNotes1" PRIMARY KEY,
"note" text NOT NULL,
"noteAdded" timestamp DEFAULT now() NOT NULL,
"addedByID" int8 NOT NULL,
"prospectID" int8 NOT NULL,
CONSTRAINT "FK_prospectNotes_1" FOREIGN KEY ("prospectID") REFERENCES
"users" ("userID"),
CONSTRAINT "FK_prospectNotes_2" FOREIGN KEY ("addedByID") REFERENCES "users"
("userID"));

COMMENT ON COLUMN "prospectNotes"."addedByID" IS 'userid of author of this
note';
COMMENT ON COLUMN "prospectNotes"."prospectID" IS 'userid of prospect this
note is about';

create or replace function "v_i_prospectNotes"() returns opaque as '
declare
errors text := '''';
checkRec RECORD;
begin
if NEW."prospectNoteID" IS NULL then
errors = errors || ''\\nprospectNoteID\\tmust not be empty.'';
end if;

if NEW."note" IS NULL then
errors = errors || ''\\nnote\\tmust not be empty.'';
end if;

if NEW."addedByID" IS NULL then
errors = errors || ''\\naddedByID\\tmust not be empty.'';
else
select into checkRec count(*) as c from "users" where
"userID"=NEW."addedByID";
if checkRec.c=0 then
errors = errors || ''\\naddedByID\\tentered does not exist.'';
end if;
end if;

if NEW."prospectID" IS NULL then
errors = errors || ''\\nprospectID\\tmust not be empty.'';
else
select into checkRec count(*) as c from "users" where
"userID"=NEW."prospectID";
if checkRec.c=0 then
errors = errors || ''\\nprospectID\\tentered does not exist.'';
end if;
end if;

if errors != '''' then
raise exception ''%'',errors;
end if;

return NEW;
end;
' language 'plpgsql';

create or replace function "v_u_prospectNotes"() returns opaque as '
declare
errors text := '''';
checkRec RECORD;
begin
raise exception ''You may not edit notes!'';
end;
' language 'plpgsql';

create trigger "t_v_i_prospectNotes" before insert on "prospectNotes" for
each row execute procedure "v_i_prospectNotes"();

create trigger "t_v_u_prospectNotes" before update on "prospectNotes" for
each row execute procedure "v_u_prospectNotes"();

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2003-01-12 05:19:15 Re: ERROR: NEW used in non-rule query
Previous Message Bruno Wolff III 2003-01-12 00:51:21 Re: Reference to multiple cols