Re: PL/pgSQL "compilation error"

On 3/14/01, 4:28:03 PM, Josh Berkus <josh(at)agliodbs(dot)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