Re: PL/pgSQL "compilation error" - Mailing list pgsql-sql

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