Thread: trigger compile problem
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
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
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
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
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
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
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
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
> 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
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
> 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