Thread: Problem assigning return value from function to a variable
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
Note: I successfully called the function using SELECT INTO getpercentiles get_facility_percentiles(_wagerateid); And the function runs through fine, and I return, I was going to print out RAISE NOTICE ''DONE CALLING FUNCTION ''; But I receive the following error. ERROR: Cannot display a value of type RECORD On Wed, 28 Jul 2004 15:02:24 -0600 Betsy Barker <betsy.barker@supportservicesinc.com> wrote: > I'm having trouble calling a function that returns a custom type. The functions and the custom type are created fine inthe 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 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Betsy Barker IT Manager Support Services, Inc (720)489-1630 X 38
PLEASE HELP.
I've read and read and read and have tried all of the example calls. I need to call a pl/pgsql function that returns a
customtype. Just one 'row'. That is all. How do I call it?
The function is defined as CREATE OR REPLACE FUNCTION get_facility_percentiles(INTEGER) RETURNS public."percentiles" AS
'
I've tried about 20 different calls. None work.
--SELECT getpercentiles FROM get_facility_percentiles(_wagerateid) AS (getpercentiles RECORD);
--SELECT * FROM get_facility_percentiles(_wagerateid) AS (getpercentiles RECORD);
--SELECT * FROM get_facility_percentiles(_wagerateid) AS (fifthpct FLOAT ,twentyfifth FLOAT, fiftieth
FL
OAT, seventyfifth FLOAT, ninetyfifth FLOAT);
--SELECT f1,f2,f3,f4,f5 FROM get_facility_percentiles(_wagerateid) AS (f1 FLOAT,f2 FLOAT,f3 FLOAT,f4
FLO
AT,f5 FLOAT);
--SELECT
getpercentiles.fifthpct,getpercentiles.twentyfifth,getpercentiles.fiftieth,getpercentiles.seven
tyfifth,getpercentiles.ninetyfifth FROM get_facility_percentiles(_wagerateid);
--SELECT * FROM get_facility_percentiles(_wagerateid) AS (getpercentiles RECORD);
--SELECT * FROM get_facility_percentiles(_wagerateid) AS (getpercentiles public."percentiles");
---SELECT * FROM get_facility_percentiles(_wagerateid) AS (f1 FLOAT ,f2 FLOAT, f3 FLOAT, f4 FLOAT, f5
FL
OAT);
--SELECT getpercentiles FROM get_facility_percentiles(_wagerateid) AS (getpercentiles
public."percentile
s");
--calls function, but errors out at return with cannot display a value of type RECORD
--SELECT INTO getpercentiles get_facility_percentiles(_wagerateid);
--SELECT * FROM get_facility_percentiles(_wagerateid) AS pcts(f1 public."percentiles".fifthpct,f2
public."percentiles".twentyfifth,f3 public."percentiles".fiftieth, f4 public."percentiles".seventyfifth, f5
public."percentiles".ninetyfifth);
Thank you.
Betsy Barker
On Wed, 28 Jul 2004 15:38:22 -0600
Betsy Barker <betsy.barker@supportservicesinc.com> wrote:
> Note:
> I successfully called the function using
> SELECT INTO getpercentiles get_facility_percentiles(_wagerateid);
> And the function runs through fine, and I return, I was going to print out
> RAISE NOTICE ''DONE CALLING FUNCTION '';
> But I receive the following error.
>
> ERROR: Cannot display a value of type RECORD
>
>
>
> On Wed, 28 Jul 2004 15:02:24 -0600
> Betsy Barker <betsy.barker@supportservicesinc.com> wrote:
>
> > I'm having trouble calling a function that returns a custom type. The functions and the custom type are created
finein 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
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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
> --
> Betsy Barker
> IT Manager
> Support Services, Inc
> (720)489-1630 X 38
>
--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38