Problem assigning return value from function to a variable - Mailing list pgsql-novice

From Betsy Barker
Subject Problem assigning return value from function to a variable
Date
Msg-id 20040728150224.367a0763.betsy.barker@supportservicesinc.com
Whole thread Raw
Responses Re: Problem assigning return value from function to a  (Betsy Barker <betsy.barker@supportservicesinc.com>)
List pgsql-novice
I'm having trouble calling a function that returns a custom type. The functions and the custom type are created fine in
thedatabase,  
but I receive a runtime error.

[I am trying to speed up the function by calculating all 5 values and returning them at once, instead of calling a
functionfive times and  
returning one value at a time ]

Here is the error:

ssi=> select get_associations();
NOTICE:  The get_associations function began 2004-07-28 14:53:55.953142
NOTICE:   Working on association:10
NOTICE:  The get_facilities() function began
NOTICE:   Working on facilityid:491
WARNING:  plpgsql: ERROR during compile of calc_facilities near line 171
WARNING:  Error occurred while executing PL/pgSQL function get_facilities_by_association
WARNING:  line 12 at assignment
ERROR:  syntax error at or near "getpercentiles"

The line that is in error is the line where I call the function below and try to assign it to a variable defined as
public."percentiles"%ROWTYPE.

========================== CALLED FUNCTION ================================================
CREATE OR REPLACE FUNCTION get_facility_percentiles(INTEGER) RETURNS public."percentiles" AS '
DECLARE
        wrid            ALIAS FOR $1;
        fifthpct        FLOAT;
        twentyfifthpct  FLOAT;
        fiftiethpct     FLOAT;
        seventyfifthpct FLOAT;
        ninetyfifthpct  FLOAT;
        rtnpercentiles  public."percentiles"%ROWTYPE;


========================= ALL LOGIC HERE TO DETERMINE percentiles===============================


                rtnpercentiles.fifth            := fifthpct;
                rtnpercentiles.twentyfifth      := twentyfifthpct;
                rtnpercentiles.fiftieth         := fiftiethpct;
                rtnpercentiles.seventyfifth     := seventyfifthpct;
                rtnpercentiles.ninetyfifth      := ninetyfifthpct;

                RETURN rtnpercentiles;
END;
' LANGUAGE 'plpgsql';


========================CALLING FUNCTION WITH DECLARATION FOR RETURN VARIABLE====================

DECLARE
        getpercentiles  public."percentiles"%ROWTYPE;

                getpercentiles  := get_facility_percentiles(_wagerateid); ----> THE LINE THAT CAUSES THE ERROR

===============================================================================================
Here is the custom type declaration:
CREATE type percentiles as
(
        fifthpct        FLOAT,
        twentyfifth     FLOAT,
        fiftieth        FLOAT,
        seventyfifth    FLOAT,
        ninetyfifth     FLOAT
);






--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38

pgsql-novice by date:

Previous
From: Rafael Charnovscki
Date:
Subject: Re: Varchar or integer primary key?
Next
From: Betsy Barker
Date:
Subject: Re: Problem assigning return value from function to a