Re: ERROR: pg_class_aclcheck: relation (OID) not found with SELECT INTO variable - Mailing list pgsql-novice

----- Original Message -----
From: "Betsy Barker" <betsy.barker@supportservicesinc.com>
To: <pgsql-novice@postgresql.org>
Sent: Friday, July 23, 2004 10:27 AM
Subject: [NOVICE] ERROR: pg_class_aclcheck: relation (OID) not found with
SELECT INTO variable


> Hi,
> I'm building a function that utilizes a temporary table and I am doing a
SELECT INTO variable from the table. I've read several posts about the
EXECUTE,
> however I'm stuck because my error happens during the SELECT INTO a
variable, and apparently the EXECUTE SELECT INTO has not been implemented in
version 7.3.4. Here was my original statement, with the associated error:
>
>         --EXECUTE ''SELECT sum(numberemployees) INTO totemployees FROM
wages'';
>         --ERROR:  EXECUTE of SELECT ... INTO is not implemented yet
>
> So, I've switched tactics and now I get another error. This error occurs
the second time I do the SELECT INTO variable FROM TEMP TABLE. (Note, the
first time I have 1 employee it works, but the second time I have 1
employee, and use the _rate variable, I get the error)
>
> Before you look at the error and the code, let me just say that I'm trying
to write some code that would normally require array variables, because I'm
calculating percentiles. To calculate percentiles, I must be able to find a
certain row number. Using arrays, that means I would pull the wage[11] value
or something like that. I was unable to get arrays to work, and I concluded
that I cannot use an array as a variable in pl/pgsql. [It kept failing when
I would say variable[i] := value; So, I switched to temporary tables with a
row number, however this is giving me a heck of a time too. Does anyone have
a different approach that would be easier? The goal is to get a table with
say 25 rows in it, containing all different wages. Then, to get the 5th
percentile, I may go in and get the 3rd wage, to get the 25th percentile I
would go in and get the 7th wage, etc,etc. Sometimes I have to get two rows
and weight them, for instance get the 6th and the 7th rows, and perform a
function!
> . Either way, it isn't that complicated, but I have been unable to
determine a good (ie easy and it works) way to do this type of logic using
this database/language.
>
> Thank you for your help.
>
> NOTICE:  Inside calc_facility_percentiles with wagerateid:18304
> NOTICE:  Inside calc_facility_percentiles first fetch cursor
> NOTICE:  Inside calc_facility_percentiles with number of employees:2
> NOTICE:   5th:24.11
> NOTICE:  25th:23.8
> NOTICE:  50th:26.9
> NOTICE:  75th:30
> NOTICE:  95th:29.69
> NOTICE:  Working on jobcodeid:8
> NOTICE:  Inside calc_facility_percentiles with wagerateid:18311
> NOTICE:  Inside calc_facility_percentiles first fetch cursor
> NOTICE:  Inside calc_facility_percentiles with number of employees:1
> NOTICE:   5th:11.75
> NOTICE:  25th:11.75
> NOTICE:  50th:11.75
> NOTICE:  75th:11.75
> NOTICE:  95th:11.75
> NOTICE:  Working on jobcodeid:18
> NOTICE:  Inside calc_facility_percentiles with wagerateid:18385
> NOTICE:  Inside calc_facility_percentiles first fetch cursor
> NOTICE:  Inside calc_facility_percentiles with number of employees:4
> NOTICE:  #employees > 3:4
> NOTICE:   5th:<NULL>
> NOTICE:  25th:<NULL>
> NOTICE:  50th:<NULL>
> NOTICE:  75th:<NULL>
> NOTICE:  95th:<NULL>
> NOTICE:  Working on jobcodeid:20
> NOTICE:  Inside calc_facility_percentiles with wagerateid:18313
> NOTICE:  Inside calc_facility_percentiles first fetch cursor
> NOTICE:  Inside calc_facility_percentiles with number of employees:1
> WARNING:  Error occurred while executing PL/pgSQL function
calc_facility_percentiles
> WARNING:  line 84 at select into variables
> ERROR:  pg_class_aclcheck: relation 20928632 not found
>
>
============================================================================
============
>
> Here is the code:
>
>         RAISE NOTICE ''Inside calc_facility_percentiles with wagerateid:%
'',wrid;
>         CREATE TEMP TABLE wages AS SELECT 1 as rownumber
,wage,numberemployees FROM wageratedetail WHERE wagerateid = wrid;
>         EXECUTE ''DELETE FROM wages'';
>
>         OPEN wageratelist FOR SELECT wage,numberemployees FROM
wageratedetail WHERE wagerateid = wrid ORDER BY wage;
>
>         FETCH wageratelist INTO _wage,_numempl;
>
>         RAISE NOTICE ''Inside calc_facility_percentiles first fetch cursor
'';
>         WHILE FOUND LOOP
>
>                 EXECUTE ''INSERT INTO wages
(rownumber,wage,numberemployees) VALUES (''
>                 ||quote_literal(i)
>                 ||'',''
>                 ||quote_literal( _wage)
>                 ||'',''
>                 ||quote_literal( _numempl)
>                 ||'')'';
>
>                 i := i + 1;
>
>                 FETCH wageratelist INTO _wage,_numempl;
>
>         END LOOP;
>
>         CLOSE wageratelist;
>
>         SELECT sum(numberemployees) INTO totemployees FROM wageratedetail
where wagerateid = wrid;
>
>         IF NOT FOUND THEN
>                 RAISE NOTICE ''Inside calc_facility_percentiles No rate
points'';
>                 RETURN returnpct;
>         END IF;
>
>
>         RAISE NOTICE ''Inside calc_facility_percentiles with number of
employees:% '',totemployees;
>         IF totemployees = 1 THEN
>                 SELECT wage INTO _rate FROM wages;
>
>                 fifthpct        := _rate;
>                 twentyfifthpct  := _rate;
>                 fiftiethpct     := _rate;
>                 seventyfifthpct := _rate;
>                 ninetyfifthpct  := _rate;
>
>         END IF;
>         IF totemployees = 2 THEN
>                 select wage INTO _rate1 FROM wages WHERE rownumber = 1;
>                 select wage INTO _rate2 FROM wages WHERE rownumber = 2;
>
>                 fifthpct        :=      ((.95 * _rate1) + (.05 * _rate2));

>                 twentyfifthpct  :=      _rate1;
>                 fiftiethpct     :=      ((.50 * _rate1) + (.50 * _rate2));
>                 seventyfifthpct :=      _rate2;
>                 ninetyfifthpct  :=      ((.05 * _rate1) + (.95 * _rate2));
>
>         END IF;
>         IF totemployees >= 3 THEN
>
>         RAISE NOTICE ''#employees > 3:% '',totemployees;
>         END IF;
>
>
>        DROP TABLE wages;
>
>         RAISE NOTICE '' 5th:% '',fifthpct;
>         RAISE NOTICE ''25th:% '',twentyfifthpct;
>         RAISE NOTICE ''50th:% '',fiftiethpct;
>         RAISE NOTICE ''75th:% '',seventyfifthpct;
>         RAISE NOTICE ''95th:% '',ninetyfifthpct;
>
>
>         IF pcttype = ''05'' THEN
>                 returnpct       :=       fifthpct;
>         ELSIF pcttype = ''25'' THEN
>                 returnpct       :=       twentyfifthpct;
>         ELSIF pcttype = ''50'' THEN
>                 returnpct       :=       fiftiethpct;
>         ELSIF pcttype = ''75'' THEN
>                 returnpct       :=       seventyfifthpct;
>         ELSIF pcttype = ''95'' THEN
>                 returnpct       :=       ninetyfifthpct;
>         END IF;
>
>         RETURN returnpct;
> END;
>
> ' LANGUAGE 'plpgsql';
>
>
> --
> Betsy Barker
> IT Manager
> Support Services, Inc
> (720)489-1630 X 38
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>