I've just written a function in PL/pgSQL that creates two temporary tables, then drops them at the end. For each
session,the first time I run it works great. The second time I try it from the same session, I get this error:
trans=# SELECT * FROM tranddthistory(6, '2003-03-20', '2003-04-05') AS (senddate date, day char(3), filename
varchar(40),postingdate date, systemdate date) ;
WARNING: Error occurred while executing PL/pgSQL function tranddthistory
WARNING: line 19 at SQL statement
ERROR: pg_class_aclcheck: relation 89979461 not found
trans=#
I feel pretty confident that I'm doing something wrong with the temporary tables. I've read everything I can find in
thedocs, searched the list archives, and come up empty. Any ideas?
TIA,
Roman Fail
Environment: PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
The function:
CREATE OR REPLACE FUNCTION public.tranddthistory(int4, timestamp, timestamp) RETURNS SETOF record AS '
DECLARE
vclientid ALIAS FOR $1;
vstartdate ALIAS FOR $2;
venddate ALIAS FOR $3;
vrec record;
vcurrdate DATE;
vfiledate DATE;
vdow int4;
vday CHAR(3);
BEGIN
/* Create a temporary table that has a record for
each day of the week where we expect to receive a file.
Sunday = 0, Monday = 1, etc.
*/
CREATE TEMPORARY TABLE ttclientdays (dow int4);
/* METAVANTE, Monday to Friday */
IF vclientid = 6 THEN
INSERT INTO ttclientdays (dow) VALUES (1);
INSERT INTO ttclientdays (dow) VALUES (2);
INSERT INTO ttclientdays (dow) VALUES (3);
INSERT INTO ttclientdays (dow) VALUES (4);
INSERT INTO ttclientdays (dow) VALUES (5);
END IF;
/* SOUTHTRUST, Sunday to Friday */
IF vclientid = 316 THEN
INSERT INTO ttclientdays (dow) VALUES (0);
INSERT INTO ttclientdays (dow) VALUES (1);
INSERT INTO ttclientdays (dow) VALUES (2);
INSERT INTO ttclientdays (dow) VALUES (3);
INSERT INTO ttclientdays (dow) VALUES (4);
INSERT INTO ttclientdays (dow) VALUES (5);
END IF;
/* Create a temporary table with a record for
each day during the specified date range where
we would expect to receive a file
for this client
*/
vcurrdate := vstartdate;
CREATE TEMPORARY TABLE ttfiledates (filedate DATE, day CHAR(3));
WHILE vcurrdate <= venddate LOOP
vdow := date_part(''dow'', vcurrdate);
IF vdow IN (SELECT dow FROM ttclientdays) THEN
IF vdow = 0 THEN vday := ''SUN''; END IF;
IF vdow = 1 THEN vday := ''MON''; END IF;
IF vdow = 2 THEN vday := ''TUE''; END IF;
IF vdow = 3 THEN vday := ''WED''; END IF;
IF vdow = 4 THEN vday := ''THU''; END IF;
IF vdow = 5 THEN vday := ''FRI''; END IF;
IF vdow = 6 THEN vday := ''SAT''; END IF;
INSERT INTO ttfiledates (filedate, day) VALUES (vcurrdate, vday);
END IF;
vcurrdate = vcurrdate + interval ''1 day'';
END LOOP;
FOR vrec IN
SELECT DISTINCT filedate, day, filename, postingdate, systemdate
FROM ttfiledates
LEFT JOIN tranheader ON filedate = systemdate
WHERE systemdate BETWEEN vstartdate AND venddate
AND clientid = vclientid
ORDER BY filedate
LOOP
RETURN NEXT vrec;
END LOOP;
DROP TABLE ttclientdays;
DROP TABLE ttfiledates;
RETURN;
END;
' LANGUAGE 'plpgsql' STABLE;
