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
-- so we 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_agg SET 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
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, 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
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!
Tim
--
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
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
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 = 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;
END IF;
RETURN NEW;
END;
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
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 19026Import Notes
Reply to msg id not found: Pine.LNX.4.30.0103141752500.1211-100000@peter.localdomain | Resolved by subject fallback
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, Pennsylvania 19026
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