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

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