Re: Calling a function that returns a composite type using

#1

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.barker (AT) supportservicesinc (DOT) com> wrote:

Quote:

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 (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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Calling a function that returns a composite type using

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: Calling a function that returns a composite type using - 07-29-2004 , 01:32 PM