Re: Calling a function that returns a composite type using - Mailing list pgsql-novice

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