Re: Assigning a return value from a function to a

Tom,
Thank you. That was the problem. I changed all the ELSE IF to ELSIF and then with just one END IF the parser stopped complaining.

I appreciate your help very much!

Sincerely,
Betsy Barker

>From Tom:
I think the problem is that you wrote ELSE IF instead of ELSIF.
When you write ELSE IF, the plpgsql parser is going to expect a separate
END IF to match that IF, so it thinks you are short a boatload of END
IFs; but it has no reason to complain until it gets to the end of the
function.

regards, tom lane
>From Betsy:
> I did check the syntax at the end of the function and couldn't find
> anything. Here is the whole function and I apologize for what it looks
> like.

On Fri, 16 Jul 2004 11:24:00 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com> writes:
> > The calling function, calc_facilities, runs fine without the call to calc_facility_percentiles. I spent a lot of time commenting out and putting in extra lines as a diagnostic to determine if it really was the call to the function or if it was a missing ' or ; somewhere. And it is the call.
>
> You are not reading the error messages carefully enough. Some of them
> are complaining about your call syntax (in the cases where it was bad)
> but the rest are complaining about a separate syntax error inside the
> called function.
>
> For instance, this statement is not good (one SELECT too many):
>
> > SELECT INTO fifthpct SELECT calc_facility_percentiles(''05'',_wagerateid);
> > This one gives:
> > WARNING: Error occurred while executing PL/pgSQL function calc_facilities
> > WARNING: line 163 at select into variables
> > ERROR: parser: parse error at or near "SELECT" at character 9
>
> but this one is fine:
>
> > fifthpct := calc_facility_percentiles(''05'',_wagerateid);
> > This one gives:
> > WARNING: plpgsql: ERROR during compile of calc_facility_percentiles near line 194
> > WARNING: Error occurred while executing PL/pgSQL function calc_facilities
> > WARNING: line 165 at assignment
> > ERROR: syntax error at or near ";"
>
> You're getting past the call and into calc_facility_percentiles, which
> has evidently got a syntax problem of its own. Unfortunately you sent
> less than 194 lines of calc_facility_percentiles, so I can't see what's
> wrong inside it.
>
> The key thing to realize here is that the WARNING lines are intended to
> give you context about where the ERROR is. So when you get an error
> down inside more than one level of function call, you'll get what
> amounts to a traceback of the calling plpgsql functions. (PG 7.4 and
> later present this information in a less confusing format, BTW.)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38