Re: 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.

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco