I am receiving the following error and cannot understand what it means.
Please let me know if this makes sense to you.
ERROR: could not open relation 1663/3364852/7973197: No such file or
directory
CONTEXT: SQL statement "create temporary table tmpcust as select a.* from
qry_Customers_Country a"
PL/pgSQL function "buildsrtemptables" line 34 at execute statement
The code I am using checks if a temporary tables exists, if it exists, it
drops 3 temp tables. The function continues on and creates the temporary
tables. This ensures that if my application calls the function twice in the
same connection, it doesn't get the error. What is strange about the error
is that it does not happen everytime. I can call the function and it creates
the tables. I then call it again and I get the error, and the tables still
exist. Then I call the function again and it drops and rebuilds the tables
with no error. But it is not always every other one, sometimes it is every 3
calls.
Below is the code:
create or replace function buildsrtemptables(uidparm int, prodparm int,
cntparm int, custparm int, orderparm int, startdateparm date, monthsparm
int) returns void
--prodparm 0=not enabled, 1=useprod, 2=useprodtype
--cntparm 0 = not enabled, 1 = usecountry, 2= useregion
--custparm 1=list, 2=all
--orderparm 0=not enabled, 1=3months, 2=1yr, 3=2yr, 4=3yr, 5=all, 6=defined
--if orderparm=6 then
--startdate date
--months int
as
$$
declare
sqltmpcust citext;
sqltmporder citext;
tempschema citext;
begin
select current_schemas[1] into tempschema from current_schemas(true);
if tempschema like 'pg_temp%' then
if exists(select * from pg_class a join pg_namespace b on
a.relnamespace=b.oid and b.nspname=tempschema where relname='tmpprod') then
execute 'drop table tmpprod';
execute 'drop table tmpcust';
execute 'drop table tmporder';
end if;
end if;
execute 'create temporary table tmpprod(like Products)';
if prodparm=0 then
execute 'insert into tmpprod select * from products';
elsif prodparm=1 then
execute 'insert into tmpprod select a.* from products a join set_prod b on
a.productid=b.id and b.setz AND b.uid=' || uidparm;
else --prodparm = 2
execute 'insert into tmpprod select a.* from products a join set_prodtype
b on a. producttypeid=b.id and b.setz and b.uid=' || uidparm;
end if;
sqltmpcust = 'select a.* from qry_Customers_Country a';
if custparm=1 then
sqltmpcust=sqltmpcust || ' join set_cust b on a.customerid=b.id and b.setz
and b.uid=' || uidparm;
end if;
if cntparm<>0 then
if cntparm=1 then
sqltmpcust=sqltmpcust || ' join set_country c on c.id=a.countryid and
c.setz and c.uid=' || uidparm;
else --cntparm=2
sqltmpcust=sqltmpcust || 'join countries c on c.countryid=a.countryid
join set_region d on d.id=c.regionid and d.setz and d.uid=' || uidparm;
end if;
end if;
execute 'create temporary table tmpcust as ' || sqltmpcust;
sqltmporder='select a.* from quotations a join tmpcust b on
a.customerid=b.customerid
join quotationitems c on c.quotationid=a.quotationid join tmpprod d on
d.productid=c.productid';
if orderparm not in (0,5) then
sqltmporder=sqltmporder || ' where orderdate >= ';
if orderparm = 1 then
sqltmporder=sqltmporder || 'current_date - interval ''3 months''';
elsif orderparm=2 then
sqltmporder=sqltmporder || 'current_date - interval ''1 year''';
elsif orderparm=3 then
sqltmporder=sqltmporder || 'current_date - interval ''2 years''';
elsif orderparm=4 then
sqltmporder=sqltmporder || 'current_date - interval ''3 years''';
elsif orderparm=6 then
if startdateparm is null then
sqltmporder=sqltmporder || 'current_date - interval ''' || monthsparm ||
' months''';
else --startdateparm
sqltmporder=sqltmporder || '''' || startdateparm || '''';
end if;
end if;
end if;
execute 'create temporary table tmporder as ' || sqltmporder;
return;
end;
$$ language 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
