Re: Assigning a return value from a function to a - Mailing list pgsql-novice

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@sss.pgh.pa.us> wrote:

> Betsy Barker <betsy.barker@supportservicesinc.com> writes:
> > The calling function, calc_facilities, runs fine without the call to calc_facility_percentiles. I spent a lot of
timecommenting out and putting in extra lines as a diagnostic to determine if it really was the call to the function or
ifit 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