Problem assigning return value from function to a variable

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

Responses

Browse pgsql-novice by date

  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?