Re: Assigning a return value from a function to a

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 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.

I even went so far as to create the silly funcparm so that I could pass the variable instead of a literal, thinking that may 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 send the 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 ORDER BY 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