Temporary table and loops in a function
I have a function in plpgsql where it creates a temp table and then has a loop. The point is that every time it loops, it also executes the part where it creates the temporary table, and therefore the error message appears:
ERROR: relation "tmpr" already exists
CONTEXT: SQL statement "CREATE TEMPORARY TABLE tmpr (
id int,
source geometry,
target geometry,
dist_ft character varying
)"
Is there a way to prevent a piece of code from being executed more than once?
Below you can find the code:
DECLARE
_r record;
t record;
i int := 0;
j int := 1;
count int := 0;
source_geom character varying;
target_geom character varying;
BEGIN
BEGIN
CREATE TEMPORARY TABLE tmpr (
id int,
source geometry,
target geometry,
dist_ft character varying
);
END;
BEGIN
CREATE TEMPORARY TABLE tmp (
ogc_fid int,
wkb_geometry character varying,
track_fid int
);
END;
-- END IF;
WHILE i < 3 --DEPENDS ON THE NUMBER OF TRACKS
LOOP
--j := 1;
--WHILE j < 29 --DEPENDS ON THE NUMBER OF TRACK POINTS
--LOOP
EXECUTE 'INSERT INTO tmp (ogc_fid, wkb_geometry, track_fid)
SELECT '|| quote_ident(gid_cname) ||' , ' ||quote_ident(geo_cname)||' , ' || quote_ident(tid_cname) ||'
FROM ' ||quote_ident(geom_table)|| '
WHERE ' ||quote_ident(tid_cname)|| ' = ' || i;
FOR _r IN EXECUTE
' SELECT *'
||' FROM tmp'
LOOP
EXECUTE 'INSERT INTO tmpr (id, source, target, dist_ft)
SELECT a.'|| quote_ident(gid_cname) || ' AS id,'
|| ' st_astext( a.'||quote_ident(geo_cname)||') AS source,'
|| ' st_astext(b.'||quote_ident(geo_cname)||') AS target, '
|| ' ST_Distance(a.'||quote_ident(geo_cname) || ' , b.'||quote_ident(geo_cname)||') As dist_ft '
|| ' FROM tmp AS a INNER JOIN tmp As b ON ST_DWithin(a.'||quote_ident(geo_cname)|| ', b.'||quote_ident(geo_cname)|| ',1000)'
|| ' WHERE b.'||quote_ident(gid_cname)|| ' > a.'||quote_ident(gid_cname)|| ' AND b.'||quote_ident(tid_cname)|| ' = '||i|| 'AND a.'||quote_ident(tid_cname)|| ' = '||i||
' ORDER BY dist_ft '
|| ' Limit 1 ';
--source_geom := temp.source;
--target_geom := temp.target;
EXECUTE 'update ' || quote_ident(geom_table) ||
' SET source = tmpr.source
, target = tmpr.target
FROM tmpr
WHERE ' || quote_ident(gid_cname) || ' = tmpr.id';
EXECUTE 'delete from tmpr';
END LOOP;
--j = j + 1;
--END LOOP;
EXECUTE 'delete from tmp';
i = i + 1;
END LOOP;
RETURN 'OK';
END;
source to share
You can use a suggestion IF NOT EXISTS
to avoid an exception (introduced with pg 9.1):
CREATE TEMPORARY TABLE IF NOT EXISTS tmpr (...);
Better to check if the table has rows in this case:
IF EXISTS (SELECT 1 FROM tmpr) THEN -- table itself exists after above command
DELETE FROM tmpr;
END IF;
To avoid later calling the function, or altogether if you no longer need the temp table after the function ends, add ON COMMIT DROP
:
CREATE TEMPORARY TABLE IF NOT EXISTS tmpr (...) ON COMMIT DROP;
This will still fail if you call the function repeatedly within the same transaction. In this case, you can add explicit expressions DROP TABLE
to the end of your function.
source to share