Thread: Assigning a return value from a function to a variable.
Hello, Even though I'm new to postgresql, I've been a programmer for 18+ years. Working mainly with Unix and databases
(Oracle,sybase, informix, db2) and languages such as C,Perl, and lately Java. I'm currently working with a J2EE system
runningon Orion working with PostgreSQL on Linux.
I am rewriting a long running J2EE piece of the application as a PL/pgSQL stored procedure and I'm having an issue with
somethingthat is as old as programming languages themselves. I'm trying to assign a return value from a function to a
variable.Sounds simple enough, but I still do not have the syntax down - after spending a day reading and searching. I
wonderif you could help.
The function is called calc_facility_percentiles and is defined to return a float. the fifthpct variable is a float.The
function actually has a return statement at the end of it that is returning a float. So, I do not know what the issue
is.I've tried SELECT INTO, I've tried SELECT INTO with another SELECT. I've tried the direct assignment of := This is
justnuts! None of them work. See all the versions I've tried below:
funcparm := ''05'';
--SELECT INTO fifthpct calc_facility_percentiles(funcparm,_wagerateid);
--SELECT INTO fifthpct SELECT calc_facility_percentiles(''05'',_wagerateid);
--fifthpct := SELECT calc_facility_percentiles(''05'',_wagerateid);
--fifthpct := calc_facility_percentiles(''05'',_wagerateid);
fifthpct := calc_facility_percentiles(funcparm,_wagerateid);
Here is the function signature:
CREATE OR REPLACE FUNCTION calc_facility_percentiles(VARCHAR(2),INTEGER) RETURNS FLOAT AS
I'm wondering if I'm not handling the varchar(2) correctly?
The error is:
Error occurred while executing PL/pgSQL function calc_facilities
WARNING: line 166 at assignment
ERROR: syntax error at or near ";"
So, I guess my question is : How to I assign the returning float value from my function to a variable?
Thank you very much for any help you can give me.
Betsy Barker
Betsy Barker <betsy.barker@supportservicesinc.com> writes:
> The error is:
> Error occurred while executing PL/pgSQL function calc_facilities
> WARNING: line 166 at assignment
> ERROR: syntax error at or near ";"
Offhand this sounds like a pretty simple syntactic error --- like one
too many semicolon, or one too few --- but there's no chance of
diagnosing it from the info you've given us. Could you send along the
exact text of your plpgsql function for maybe 10 lines around line 166
(and be sure to mark exactly which line is 166 down from the function
body start)?
FWIW, we're aware that plpgsql's syntactic error messages could use
work, and I think that 7.5 will do at least a bit better in this area.
But for the moment, you need to give more info to make it clear what
is going wrong.
regards, tom lane
Thanks for the response Tom.
I'm on postgreSQL 7.3.4 running on Redhat Linux.
The calling function, calc_facilities, runs fine without the call to calc_facility_percentiles. I spent a lot of time
commentingout and putting in extra lines as a diagnostic to determine if it really was the call to the function or if
itwas a missing ' or ; somewhere. And it is the call.
I even went so far as to create the silly funcparm so that I could pass the variable instead of a literal, thinking
thatmay have messed things up.
So, here are the calls - and their associated errors:
-- funcparm := ''05'';
SELECT INTO fifthpct calc_facility_percentiles(funcparm,_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 162 at select into variables
ERROR: syntax error at or near ";"
--------------------------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------------------------
fifthpct := SELECT calc_facility_percentiles(''05'',_wagerateid);
This one gives:
WARNING: Error occurred while executing PL/pgSQL function calc_facilities
WARNING: line 164 at assignment
ERROR: parser: parse error at or near "SELECT" at character 9
----------------------------------------------------------------------------------------------------
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 ";"
Here is the end of the code after the function call, and the beginning of the calc_facility_percentiles function. I can
sendthe whole thing if you want, in an attachment.
Thank you Tom. I'm so frustrated! Am I handling the varchar(2) correctly with the ''05''?
-------------------------------------
funcparm := ''05'';
--SELECT INTO fifthpct calc_facility_percentiles(funcparm,_wagerateid);
--SELECT INTO fifthpct SELECT calc_facility_percentiles(''05'',_wagerateid);
--fifthpct := SELECT calc_facility_percentiles(''05'',_wagerateid);
--fifthpct := calc_facility_percentiles(''05'',_wagerateid);
fifthpct := calc_facility_percentiles(funcparm,_wagerateid);
-- twentyfifthpct := calc_facility_percentiles(''25'',_wagerateid);
-- fiftiethpct := calc_facility_percentiles(''50'', _wagerateid);
-- seventyfifthpct := calc_facility_percentiles(''75'', _wagerateid);
-- ninetyfifthpct := calc_facility_percentiles(''95'',_wagerateid);
INSERT INTO new_calculation VALUES
(calcid,_jobcodeid,_wagerateid,NULL,NULL,_facilityid,_entrycycle,_numhospitals,_curavgm
in,_prioravgmin,_wr2yravgmin,_wr3yravgmin,_wr4yravgmin,_wrdiffavgmin,_curavgmax,_numhospitals,_numemployees,_priorwtdavg,_curwtdavg,_act2y
rwtdavg,_act3yrwtdavg,_act4yrwtdavg,_actdiffwtdavg,NULL,NULL,NULL,NULL,calcdate,NULL,NULL);
FETCH calcs INTO _formalrange,
_jobcodeid,_wagerateid,_facilityid,_dataentryid,_entrycycle,_numhospitals,_cursumlow,_curcntlow,_cu
rsumhigh,_curcnthigh,_numemployees,_sumgrossamt;
END LOOP;
CLOSE calcs;
RETURN ''0'';
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION calc_facility_percentiles(VARCHAR(2),INTEGER) RETURNS FLOAT AS '
DECLARE
pcttype ALIAS FOR $1; -- to figure out which percentile to return since arrays do not work as variables
wrid ALIAS FOR $2;
totemployees INTEGER;
_rate FLOAT;
_wage FLOAT;
_numempl INTEGER;
i INTEGER := 1;
j INTEGER := 1;
index1 INTEGER := 1;
index2 INTEGER := 1;
weight FLOAT;
_rate1 FLOAT;
_rate2 FLOAT;
fifthpct FLOAT;
twentyfifthpct FLOAT;
fiftiethpct FLOAT;
seventyfifthpct FLOAT;
ninetyfifthpct FLOAT;
returnpct FLOAT;
wageratelist refcursor;
BEGIN
-- load up wages into a temp table first
-- order of wages is important
RAISE NOTICE ''Inside calc_facility_percentiles with wagerateid:% '',wrid;
CREATE TEMP TABLE wages (rownumber integer,
wage float,
numemployees integer);
OPEN wageratelist FOR SELECT wage,numberemployees FROM wageratedetail WHERE wagerateid = wrid ORDER BY wage;
FETCH wageratelist INTO _wage,_numempl;
WHILE FOUND LOOP
INSERT INTO wages (i, _wage, _numempl);
i := i + 1;
FETCH wageratelist INTO _wage,_numempl;
END LOOP;
CLOSE wageratelist;
--CREATE TEMP TABLE foo AS SELECT count(*),wage,numberemployees FROM wageratedetail WHERE wagerateid = wrid
ORDERBY wage;
EXECUTE SELECT sum(numemployees) INTO totemployees FROM wages;
RAISE NOTICE ''Inside calc_facility_percentiles with number of employees:% '',totemployees;
IF totemployees = 1 THEN
SELECT wage INTO _rate FROM wages;
fifthpct := _rate;
twentyfifthpct := _rate;
fiftiethpct := _rate;
seventyfifthpct := _rate;
ninetyfifthpct := _rate;
ELSE IF totemployees = 2 THEN
select wage INTO _rate1 FROM wages WHERE rownumber = 1;
select wage INTO _rate2 FROM wages WHERE rownumber = 2;
fifthpct := ((.95 * _rate1) + (.05 * _rate2));
twentyfifthpct := _rate1;
fiftiethpct := ((.50 * _rate1) + (.50 * _rate2));
seventyfifthpct := _rate2;
ninetyfifthpct := ((.05 * _rate1) + (.95 * _rate2));
ELSE IF totemployees >= 3 THEN
----------------------------------------------------------
--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38
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 time
commentingout and putting in extra lines as a diagnostic to determine if it really was the call to the function or if
itwas 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
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