From: | Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Problem assigning return value from function to a variable |
Date: | 2004-07-28 21:02:24 |
Message-ID: | 20040728150224.367a0763.betsy.barker@supportservicesinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I'm having trouble calling a function that returns a custom type. The functions and the custom type are created fine in the database,
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 function five 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
From | Date | Subject | |
---|---|---|---|
Next Message | Betsy Barker | 2004-07-28 21:38:22 | Re: Problem assigning return value from function to a |
Previous Message | Rafael Charnovscki | 2004-07-27 13:33:45 | Re: Varchar or integer primary key? |