Thread: PL/pgSQL "compilation error"
Hello all - I apologize for the newbie-esque question, but the debug output
from postgres when you have a bug in your PL/pgSQL procedure is none to
detailed.
I've created the following procedure and am getting an error when I try to
update the table. The error is something like "parse error near ; on line 50".
Line 50 is the last line.
There's probably something glaring wrong in here that I'm not seeing, but any
help would be appreciated.
I don't know if the \ at the end of the line is a problem, but those were
added late in the game and didn't change the error message ;-)
Tim
CREATE FUNCTION artifactgroup_update_agg () RETURNS OPAQUE AS '
BEGIN -- -- see if they are moving to a new artifacttype -- if so, its a more complex operation -- IF
NEW.group_artifact_id<> OLD.group_artifact_id THEN -- -- transferred artifacts always have a status of 1 --
sowe will increment the new artifacttypes sums -- UPDATE artifact_counts_agg SET count=count+1,
open_count=open_count+1\ WHERE group_artifact_id=NEW.group_artifact_id;
-- -- now see how to increment/decrement the old types sums -- IF NEW.status_id <> OLD.status_id
THEN IF OLD.status_id = 2 THEN UPDATE artifact_counts_agg SET count=count-1 \ WHERE
group_artifact_id=OLD.group_artifact_id; -- -- no need to do anything if it was in deleted status
-- END IF; ELSE -- -- Was already in open status before -- UPDATE
artifact_counts_aggSET count=count-1, open_count=open_count-1 \ WHERE
group_artifact_id=OLD.group_artifact_id; END IF; ELSE -- -- just need to evaluate the status flag and
--increment/decrement the counter as necessary -- IF NEW.status_id <> OLD.status_id THEN IF
new.status_id= 1 THEN UPDATE artifact_counts_agg SET open_count=open_count+1 \ WHERE
group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 2 THEN UPDATE artifact_counts_agg SET
open_count=open_count-1\ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 3
THEN UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ WHERE
group_artifact_id=new.group_artifact_id; END IF; END IF; END IF; RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER artifactgroup_update_trig AFTER UPDATE ON artifact FOR EACH ROW EXECUTE PROCEDURE
artifactgroup_update_agg();
--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
> On Wed, Mar 14, 2001 at 10:33:49AM -0500, Bruce Momjian wrote: > > It is hard to test this without the table schema, but I think you are > > missing some END IF's in the code above. Those are not elif's, but > > actual new IF's that need their own END IF, I think. > > Oh wow - this is almost like going back to my COBOL days. > > IF NEW.status_id <> OLD.status_id THEN > IF new.status_id = 1 THEN > UPDATE artifact_counts_agg SET open_count=open_count+1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE > IF new.status_id = 2 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE > IF new.status_id = 3 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > END IF; > END IF; > END IF; > END IF; > > Yes - nesting those IFs did the trick. Are there more examples of PL/pgSQL > anywhere beyond those on your website? > > Thanks, Bruce! > There is a PL/PgSQL HowTo that we are trying to integrate into the docs for 7.1. Anyone have a URL or status on that? -- 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
> Don't you have one END IF too many?
No you don't ... I can't count this morning :-(
Weird. I don't see what's wrong either. Anyone?
regards, tom lane
Don't you have one END IF too many?
regards, tom lane
Tim,
> Hello all - I apologize for the newbie-esque question, but the debug output
> from postgres when you have a bug in your PL/pgSQL procedure is none to
> detailed.
<laugh> It's an improvement over the query parser errors -- at least in
PL/pgSQL you get a line number! Although in your case, the line number
is deceptive.
This brings up an important point. We have a medium-large user base for
PL/pgSQL out there, but it appears that Jan Wieck no longer has time to
develop the language ... nor should he be the sole developer. Howe do
we find more developers to expand & extend PL/pgSQL? I'd be willing to
contribute financially, but can't afford to actuall hire somebody on my
own (and don't have the moxie to doe the programming!).
> I've created the following procedure and am getting an error when I try to
> update the table. The error is something like "parse error near ; on line 50".
> Line 50 is the last line.
Actually, your question is not newbie-esque. That's quite a
sophisticated procedure!
THe problem is simple. PL/pgSQL at this time does not support the "ELSE
IF" structure. Thus, the compiler is looking for more "END IF"s that it
doesn't find, and errors out when it gets to the end of the procedure
without seeing them.
Thus, your final program section needs to be structured like this:
-- -- just need to evaluate the status flag and -- increment/decrement the counter as necessary --
IF NEW.status_id <> OLD.status_id THEN IF new.status_id = 1 THEN UPDATE artifact_counts_agg SET
open_count=open_count+1\ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id =
2THEN UPDATE artifact_counts_agg SET open_count=open_count-1 \ WHERE
group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 3 THEN UPDATE
artifact_counts_aggSET
open_count=open_count-1,count=count-1 \ WHERE group_artifact_id=new.group_artifact_id; END IF;
ENDIF; END IF; END IF; END IF; RETURN NEW;
END;
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
On Wed, Mar 14, 2001 at 10:33:49AM -0500, Bruce Momjian wrote: > It is hard to test this without the table schema, but I think you are > missing some END IF's in the code above. Those are not elif's, but > actual new IF's that need their own END IF, I think. Oh wow - this is almost like going back to my COBOL days. IF NEW.status_id <> OLD.status_id THEN IF new.status_id = 1 THEN UPDATE artifact_counts_agg SET open_count=open_count+1\ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 2 THEN UPDATE artifact_counts_agg SET open_count=open_count-1 \ WHERE group_artifact_id=new.group_artifact_id; ELSE IF new.status_id = 3 THEN UPDATE artifact_counts_aggSET open_count=open_count-1,count=count-1 \ WHERE group_artifact_id=new.group_artifact_id; END IF; END IF; END IF; END IF; Yes - nesting those IFs did the trick. Are there more examples of PL/pgSQL anywhere beyond those on your website? Thanks, Bruce! Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems
> IF NEW.status_id <> OLD.status_id THEN > IF new.status_id = 1 THEN > UPDATE artifact_counts_agg SET open_count=open_count+1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE IF new.status_id = 2 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > ELSE IF new.status_id = 3 THEN > UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ > WHERE group_artifact_id=new.group_artifact_id; > END IF; > END IF; > END IF; > RETURN NEW; It is hard to test this without the table schema, but I think you are missing some END IF's in the code above. Those are not elif's, but actual new IF's that need their own END IF, I think. -- 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, Pennsylvania19026
From: "Tim Perdue" <tim@perdue.net> > Hello all - I apologize for the newbie-esque question, but the debug output > from postgres when you have a bug in your PL/pgSQL procedure is none to > detailed. > > I've created the following procedure and am getting an error when I try to > update the table. The error is something like "parse error near ; on line 50". > Line 50 is the last line. You're writing 50 line functions and you're calling yourself a newbie? Hate to think what that makes me. > There's probably something glaring wrong in here that I'm not seeing, but any > help would be appreciated. Nothing huge leaps out at me except for the \ and the occasional lower-case 'new' - don't know if 'new' is the same as 'NEW' - not sure I've tried it. I have used statements on multiple lines without the \ though, so it might be worth snipping them and seeing what happens. > I don't know if the \ at the end of the line is a problem, but those were > added late in the game and didn't change the error message ;-) [snip] > UPDATE artifact_counts_agg SET open_count=open_count-1 \ > WHERE group_artifact_id=new.group_artifact_id; Failing that, email me the definition of artifacts_count_agg and artifact and I'll play with it here. - Richard Huxton
> Bruce Momjian writes: > > > There is a PL/PgSQL HowTo that we are trying to integrate into the docs > > for 7.1. Anyone have a URL or status on that? > > It's not a PL/pgSQL Howto, it's just a documentation how to port from > Oracle's PL/SQL. The status is "done". The author expressed interest in > more work on the PL/pgSQL documentation; we'll see what comes of it. Oh, that's great. Thanks. > http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- 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 writes: > There is a PL/PgSQL HowTo that we are trying to integrate into the docs > for 7.1. Anyone have a URL or status on that? It's not a PL/pgSQL Howto, it's just a documentation how to port from Oracle's PL/SQL. The status is "done". The author expressed interest in more work on the PL/pgSQL documentation; we'll see what comes of it. http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On 3/14/01, 4:28:03 PM, Josh Berkus <josh@agliodbs.com> wrote regarding Re: [SQL] PL/pgSQL "compilation error": > Tim, > > Hello all - I apologize for the newbie-esque question, but the debug output > > from postgres when you have a bug in your PL/pgSQL procedure is none to > > detailed. > <laugh> It's an improvement over the query parser errors -- at least in > PL/pgSQL you get a line number! Although in your case, the line number > is deceptive. Line numbers always are deceptive when it comes to bracketing/quotes/structure. And you can get line-numbers on a query if you stick it in a text-file and use psql -f. > This brings up an important point. We have a medium-large user base for > PL/pgSQL out there, but it appears that Jan Wieck no longer has time to > develop the language ... nor should he be the sole developer. Howe do > we find more developers to expand & extend PL/pgSQL? I'd be willing to > contribute financially, but can't afford to actuall hire somebody on my > own (and don't have the moxie to doe the programming!). There are four issues with plpgsql: 1. features (not too bad, fine for most trigger functions, wouldn't want to write an OS in it). 2. error messages (could be better, although it might be that I'm just not used to them yet) 3. documentation (someone was doing a plpgsql cookbook - is (s)he still about?) 4. quoting literal strings (drives me up the *~!%&ing wall - have you tried putting a literal string in an execute?) This is partly growing pains with PG I'd guess, look at the changes since 6.early - PG is moving into the bigtime (I can remember Ingres from University days :-) I'd be loath for plpgsql to get carried away with functionality. I like Perl a lot, but I'm not sure I want to kick off 4M of interpreter every time someone inserts on a view. I'm happy to do my bit on the documentation side, but I don't want to end up duplicating the cookbook - does anyone know the status of this? I'd guess 90% of plpgsql functions will be on the same "theme" as something in a cookbook so it's the perfect format for learning/cut & paste. > > I've created the following procedure and am getting an error when I try to > > update the table. The error is something like "parse error near ; on line 50". > > Line 50 is the last line. > Actually, your question is not newbie-esque. That's quite a > sophisticated procedure! To be honest Josh, that was my opinion. > THe problem is simple. PL/pgSQL at this time does not support the "ELSE > IF" structure. Thus, the compiler is looking for more "END IF"s that it > doesn't find, and errors out when it gets to the end of the procedure > without seeing them. Missed that completely - reading my expectations, not the code. - Richard Huxton
> I'm happy to do my bit on the documentation side, but I don't want to end > up duplicating the cookbook - does anyone know the status of this? I'd > guess 90% of plpgsql functions will be on the same "theme" as something > in a cookbook so it's the perfect format for learning/cut & paste. I am told that the cookbook has been merged into the 7.1beta docs. -- 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, Pennsylvania19026
Josh Berkus wrote: > This brings up an important point. We have a medium-large user base for > PL/pgSQL out there, but it appears that Jan Wieck no longer has time to > develop the language ... nor should he be the sole developer. Howe do > we find more developers to expand & extend PL/pgSQL? I'd be willing to > contribute financially, but can't afford to actuall hire somebody on my > own (and don't have the moxie to doe the programming!). Let me put it this way: PL/pgSQL never was something I wrote because I wanted to have it. Funny as it is, after I created PL/Tcl there where just as many questions about something independant of other packages as you can think of, and I simply wanted to get rid of these questions. This doesn't mean I'm uninterested in PL/pgSQL getting better. On the doc's it's just that I've been it's initial developer, not it's best user. So this kinda cookbook should've been better written by someone else and I see it found it's way. I definitely have plans to improve it after 7.1. This is a brief list of things I want to accomplish: 1. support for tuple and setof-tuple returns for full stored procedures. 2. Enhancement of the SPI manager and using these for real CURSOR support and from within FOR etc. loop handling. 3. Further enhancement of the SPI manager to detect usage of temporary objects (like temp tables) in a query, suppress plan preparation on those statements for better support of dynamic SQL. You're right in that there could be more PL/pgSQL handler developers. There have been contributions in the past, and that it have been a few only might be my fault not telling enough about the internals of the handler - it's an ugly piece of code anyway. Let me finish my movement to Virginia and see y'all in the 7.2 cycle. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com