I'm having problems with a trigger/function and I think it's actually a
system problem but I have no clue how to fix it. The trigger is supposed to
automatically timestamp the record when it is altered.
I've never used anything more than a sql function before so the plpgsql is
new to me.
Here's the info:
My table:
CREATE TABLE "help" (
"help_id" int4 DEFAULT nextval('help_id_seq'::text) NOT NULL,
"keyword" varchar(20) NOT NULL,
"help_text" text NOT NULL,
"auto_date" date NOT NULL,
"title" varchar(50) DEFAULT 'Help Topic',
"admin" bool DEFAULT 't',
"site_id" varchar(5) DEFAULT '0',
CONSTRAINT "help_pkey" PRIMARY KEY ("help_id")
);
My function:
CREATE FUNCTION "f_auto_date"() RETURNS OPAQUE AS '
BEGIN
NEW.auto_date := ''now'';
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
My trigger:
CREATE TRIGGER "t_auto_date" BEFORE INSERT OR UPDATE ON "help" FOR EACH ROW
EXECUTE PROCEDURE "f_auto_date"();
I'm totally fine up to this point... then I try this:
UPDATE help SET site_id = 'APW' WHERE help_id = 2;
I get the following error:
NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1
"RROR: parse error at or near "
I've tried the sample on the following web page as well:
http://www.postgresql.org/users-lounge/docs/7.0/user/c40874340.htm
And I get the exact same error (except of course the function name is
different). So I'm assuming that it's a problem in my system
configuration... I may be wrong.
I don't know how to fix this or even where to begin. I do have plpgsql
installed as a language. I'm running PG 7.0.2 on standard RedHat 7 ... not
sure of the Kernel.
Thanks for your help,
-Dan