Re: Calling a function that returns a composite type using

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 custom type. 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(dot)barker(at)supportservicesinc(dot)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(dot)barker(at)supportservicesinc(dot)com> wrote:
>
> > 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)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