Re: Assigning a return value from a function to a variable.

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