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

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
'' to mean a literal '.


--
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

On Thursday 14 December 2000 21:10, Robert B. Easter wrote:
> 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 '' to mean a literal '.

Nevermind.  I should have looked more closely at the message. %-)

--
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

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
> '' 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



> "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