Thread: trigger compile problem

trigger compile problem

From
T.R.Missner@Level3.com
Date:
Can anyone out there help me.
I have created a trigger and can't get it to run.
When it is invoked I get a compile error no matter what.
.I have changed the code in the trigger many times and it really doesn't
matter what the code actually does it still won't compile.
I have stripped it down so all it does is return NEW and still the problem.

here is the error

ERROR: parse error at or near ""

here is the code

CREATE FUNCTION "AANEW" () RETURNS opaque AS '
DECLARE myrec sipmsg_lu%ROWTYPE;
BEGIN
 SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid'';
 IF NOT FOUND THEN
  INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid'');
 END IF;
RETURN NEW;
END' LANGUAGE 'plpgsql';

any help would be great.

red hat 2.2 kernal
pgsql 7.1.3

t.r.  missner

Re: trigger compile problem

From
Doug McNaught
Date:
T.R.Missner@Level3.com writes:

> Can anyone out there help me.
> I have created a trigger and can't get it to run.
> When it is invoked I get a compile error no matter what.
> .I have changed the code in the trigger many times and it really doesn't
> matter what the code actually does it still won't compile.
> I have stripped it down so all it does is return NEW and still the problem.

It's not clear where the problem is from your post, though you seem to
have made some mistakes, which I will point out below.  Want to post
your CREATE TRIGGER statement as well?

> here is the error
>
> ERROR: parse error at or near ""
>
> here is the code
>
> CREATE FUNCTION "AANEW" () RETURNS opaque AS '
> DECLARE myrec sipmsg_lu%ROWTYPE;
> BEGIN
>  SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid'';

Why are you quoting NEW.callid?  As written above you are comparing
sipmsg_lu.callid to the literal string 'NEW.callid' which is almost
certainly not what you want.  Take the quotes off.

>  IF NOT FOUND THEN
>   INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid'');

Same here.

Otherwise nothing looks obviously wrong.

Are you writing the trigger code on a Windows machine?  Is it possible
that there are ^M characters in the text?

-Doug
--
In a world of steel-eyed death, and men who are fighting to be warm,
Come in, she said, I'll give you shelter from the storm.    -Dylan

Re: trigger compile problem

From
T.R.Missner@Level3.com
Date:
I wasn't sure about the quotes and have taken
them off ( didn't have them in the beginning )
but the problem still exists.
I was just looking at other trigger code and was
wondering if maybe I need a ; after the last END?

-----Original Message-----
From: Doug McNaught [mailto:doug@wireboard.com]
Sent: Tuesday, September 25, 2001 5:36 PM
To: Missner, T. R.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] trigger compile problem


T.R.Missner@Level3.com writes:

> Can anyone out there help me.
> I have created a trigger and can't get it to run.
> When it is invoked I get a compile error no matter what.
> .I have changed the code in the trigger many times and it really doesn't
> matter what the code actually does it still won't compile.
> I have stripped it down so all it does is return NEW and still the
problem.

It's not clear where the problem is from your post, though you seem to
have made some mistakes, which I will point out below.  Want to post
your CREATE TRIGGER statement as well?

> here is the error
>
> ERROR: parse error at or near ""
>
> here is the code
>
> CREATE FUNCTION "AANEW" () RETURNS opaque AS '
> DECLARE myrec sipmsg_lu%ROWTYPE;
> BEGIN
>  SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid'';

Why are you quoting NEW.callid?  As written above you are comparing
sipmsg_lu.callid to the literal string 'NEW.callid' which is almost
certainly not what you want.  Take the quotes off.

>  IF NOT FOUND THEN
>   INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid'');

Same here.

Otherwise nothing looks obviously wrong.

Are you writing the trigger code on a Windows machine?  Is it possible
that there are ^M characters in the text?

-Doug
--
In a world of steel-eyed death, and men who are fighting to be warm,
Come in, she said, I'll give you shelter from the storm.    -Dylan

Re: trigger compile problem

From
T.R.Missner@Level3.com
Date:
not doing this on windows FYI
solaris and linux

-----Original Message-----
From: Doug McNaught [mailto:doug@wireboard.com]
Sent: Tuesday, September 25, 2001 5:36 PM
To: Missner, T. R.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] trigger compile problem


T.R.Missner@Level3.com writes:

> Can anyone out there help me.
> I have created a trigger and can't get it to run.
> When it is invoked I get a compile error no matter what.
> .I have changed the code in the trigger many times and it really doesn't
> matter what the code actually does it still won't compile.
> I have stripped it down so all it does is return NEW and still the
problem.

It's not clear where the problem is from your post, though you seem to
have made some mistakes, which I will point out below.  Want to post
your CREATE TRIGGER statement as well?

> here is the error
>
> ERROR: parse error at or near ""
>
> here is the code
>
> CREATE FUNCTION "AANEW" () RETURNS opaque AS '
> DECLARE myrec sipmsg_lu%ROWTYPE;
> BEGIN
>  SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid'';

Why are you quoting NEW.callid?  As written above you are comparing
sipmsg_lu.callid to the literal string 'NEW.callid' which is almost
certainly not what you want.  Take the quotes off.

>  IF NOT FOUND THEN
>   INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid'');

Same here.

Otherwise nothing looks obviously wrong.

Are you writing the trigger code on a Windows machine?  Is it possible
that there are ^M characters in the text?

-Doug
--
In a world of steel-eyed death, and men who are fighting to be warm,
Come in, she said, I'll give you shelter from the storm.    -Dylan

Re: trigger compile problem

From
T.R.Missner@Level3.com
Date:
It was the ; after END
I feel like a tard.
spent over 5 hours on this .

thanks
t.r.

-----Original Message-----
From: Doug McNaught [mailto:doug@wireboard.com]
Sent: Tuesday, September 25, 2001 5:36 PM
To: Missner, T. R.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] trigger compile problem


T.R.Missner@Level3.com writes:

> Can anyone out there help me.
> I have created a trigger and can't get it to run.
> When it is invoked I get a compile error no matter what.
> .I have changed the code in the trigger many times and it really doesn't
> matter what the code actually does it still won't compile.
> I have stripped it down so all it does is return NEW and still the
problem.

It's not clear where the problem is from your post, though you seem to
have made some mistakes, which I will point out below.  Want to post
your CREATE TRIGGER statement as well?

> here is the error
>
> ERROR: parse error at or near ""
>
> here is the code
>
> CREATE FUNCTION "AANEW" () RETURNS opaque AS '
> DECLARE myrec sipmsg_lu%ROWTYPE;
> BEGIN
>  SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid'';

Why are you quoting NEW.callid?  As written above you are comparing
sipmsg_lu.callid to the literal string 'NEW.callid' which is almost
certainly not what you want.  Take the quotes off.

>  IF NOT FOUND THEN
>   INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid'');

Same here.

Otherwise nothing looks obviously wrong.

Are you writing the trigger code on a Windows machine?  Is it possible
that there are ^M characters in the text?

-Doug
--
In a world of steel-eyed death, and men who are fighting to be warm,
Come in, she said, I'll give you shelter from the storm.    -Dylan

Re: trigger compile problem

From
Tom Lane
Date:
T.R.Missner@Level3.com writes:
> It was the ; after END
> I feel like a tard.
> spent over 5 hours on this .

I've always wondered why plpgsql is so nitpickingly insistent on finding
a semicolon after the last END.  Would anyone object if I made the
last semi optional?

            regards, tom lane

Re: trigger compile problem

From
T.R.Missner@Level3.com
Date:
I certainly wouldn't.
I am wondering if somehow pgaccess left this out and I cut and paste d waht
pgaccess built.  I'll check that in the morning.

The good news is my db absolutly screems now that I moved some processing
to
a trigger.  No more reason to look at mysql which I was dreading.

thanks for all the good work Tom,
t.r. missner


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, September 25, 2001 11:23 PM
To: Missner, T. R.
Cc: Jan Wieck; doug@wireboard.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] trigger compile problem


T.R.Missner@Level3.com writes:
> It was the ; after END
> I feel like a tard.
> spent over 5 hours on this .

I've always wondered why plpgsql is so nitpickingly insistent on finding
a semicolon after the last END.  Would anyone object if I made the
last semi optional?

            regards, tom lane

Re: trigger compile problem

From
T.R.Missner@Level3.com
Date:
one more note. A lot of things I have read comparing pgsql
to mysql have stated that one of the weaknesses of pgsql is insert speed.
Well I am developing a near real time signaling analyzer and am currently
inserting over 100 rows per sec which include blobs.  I thinki I can push it
even higher
and will kepp you posted on the results if you are interested.

t.r. missner
level(3) communications



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, September 25, 2001 11:23 PM
To: Missner, T. R.
Cc: Jan Wieck; doug@wireboard.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] trigger compile problem


T.R.Missner@Level3.com writes:
> It was the ; after END
> I feel like a tard.
> spent over 5 hours on this .

I've always wondered why plpgsql is so nitpickingly insistent on finding
a semicolon after the last END.  Would anyone object if I made the
last semi optional?

            regards, tom lane

Re: trigger compile problem

From
Bruce Momjian
Date:
> T.R.Missner@Level3.com writes:
> > It was the ; after END
> > I feel like a tard.
> > spent over 5 hours on this .
>
> I've always wondered why plpgsql is so nitpickingly insistent on finding
> a semicolon after the last END.  Would anyone object if I made the
> last semi optional?

C requires the termination.  I assume our languages should too.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: trigger compile problem

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> I've always wondered why plpgsql is so nitpickingly insistent on finding
>> a semicolon after the last END.  Would anyone object if I made the
>> last semi optional?

> C requires the termination.  I assume our languages should too.

I don't think that analogy holds water at all, since plpgsql is not
C and doesn't emulate C's syntax very closely.  Even if you accept
the analogy, what we're discussing here is a semicolon after the end
of a function body, which C does not expect you to write --- so the
analogy favors omitting it, not requiring it.

            regards, tom lane

Re: trigger compile problem

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> I've always wondered why plpgsql is so nitpickingly insistent on finding
> >> a semicolon after the last END.  Would anyone object if I made the
> >> last semi optional?
>
> > C requires the termination.  I assume our languages should too.
>
> I don't think that analogy holds water at all, since plpgsql is not
> C and doesn't emulate C's syntax very closely.  Even if you accept
> the analogy, what we're discussing here is a semicolon after the end
> of a function body, which C does not expect you to write --- so the
> analogy favors omitting it, not requiring it.

Yes, good point.  Also, the end of the string is clearly marking the
_end_, so of like EOF.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026