Re: ERROR: pg_class_aclcheck: relation (OID) not found - Mailing list pgsql-novice

Hello,

pl/pgsql caches temporary tables.  When you open a sql window, execute a
function that creates a temporary table, the function ends, the temp
table still exists in memory.  If you re-execute the function in the
same sql window you will get the error message below when it tries to
recreate a table that already exists.  Of course if you never issue a
create table statement to begin with then you have no temp table to
insert into.  It is a Catch-22.

If you close the sql window the session is destroyed and so is the temp
table.

I have never come up with a way to use temp tables and pl/pgsql so that
they can be executed more than once in the same session.  I considered
somehow creating a random name for the temp table each time it is
executed but couldn't get it to work.

I have ended up creating a lot of permanent tables instead of the temp
tables.  My function first checks to see if the table exists.  If it
does not it is created.  If it does exist I execute a Delete from X to
clear it out (Truncate is not allowed from within a function).  From
their the rest of my code executes.

If you really want to use temp tables using a different language would
be the best thing to do.

Postgres does allow queries to be made across different schemas so one
could create the permanent tables which are used as temp tables in a
different schema to avoid cluttering the schema you are working in.

HTH.


On Fri, 2004-07-23 at 11:48, John Brookes wrote:
> ----- 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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org