Skip site navigation (1) Skip section navigation (2)

Re: Problem assigning return value from function to a

From: Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com>
To: Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Problem assigning return value from function to a
Date: 2004-07-28 21:38:22
Message-ID: 20040728153822.67189ed9.betsy.barker@supportservicesinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Ralf HasemannDate: 2004-07-28 22:17:41
Subject: PQfnumber and duplicate column names....
Previous:From: Betsy BarkerDate: 2004-07-28 21:02:24
Subject: Problem assigning return value from function to a variable

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group