Trigger/Function problem
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
If you look at my function definition, you can see that this is not within
the function body. This is the testing of the trigger which produces the
error. It's just a plain old SQL statment that initiates the trigger.
-Dan
----- Original Message -----
From: "Robert B. Easter" <reaster@comptechnews.com>
On Thursday 14 December 2000 21:27, Dan Wilson wrote:
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 "Try:
UPDATE help SET site_id = ''APW'' WHERE help_id = 2;
Remember that ' is used to enclose the whole function body. You have to
use
Show quoted text
'' to mean a literal '.
"Dan Wilson" <phpPgAdmin@acucore.com> writes:
I get the following error:
NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1
"RROR: parse error at or near "
Just like that, eh? It looks like the parser is spitting up on a \r
in the function text. Try saving your script with Unix-style newlines.
For 7.1 the plpgsql parser has been fixed to accept DOS-ish newlines,
but for now you gotta be careful...
regards, tom lane
That was it! Thanks Tom. I just put this functionality into phpPgAdmin and
of course it is taking the newline char from the browser's OS.
Thanks for all your help!
-Dan
Show quoted text
"Dan Wilson" <phpPgAdmin@acucore.com> writes:
I get the following error:
NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1
"RROR: parse error at or near "Just like that, eh? It looks like the parser is spitting up on a \r
in the function text. Try saving your script with Unix-style newlines.For 7.1 the plpgsql parser has been fixed to accept DOS-ish newlines,
but for now you gotta be careful...regards, tom lane