From: Josh Berkus <josh@agliodbs.com>
To: Tim Perdue <tim@perdue.net>
To: sqllist <pgsql-sql@postgresql.org>
Subject: Re: PL/pgSQL "compilation error"
Date: Wed, 14 Mar 2001 08:28:03 -0800
Message-ID: <3AAF9C13.3E8030A5@agliodbs.com> (raw)
References: <20010314085635.O670@mail.perdue.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: josh@agliodbs.com, tim@perdue.net, pgsql-sql@postgresql.org
Subject: Re: PL/pgSQL "compilation error"
In-Reply-To: <3AAF9C13.3E8030A5@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox