函数中的临时表和循环 [英] Temporary table and loops in a function
问题描述
我在plpgsql中有一个函数,它会创建一个临时表,然后有一个循环.事实是,每次循环时,它还会执行创建临时表的部分,因此弹出错误提示;
I have a function in plpgsql where it creates a temporary table and then it has a loop. The thing is that each time it loops it executes also the part where it creates the temporary table and therefore an error pops up saying;
ERROR: relation "tmpr" already exists
CONTEXT: SQL statement "CREATE TEMPORARY TABLE tmpr (
id int,
source geometry,
target geometry,
dist_ft character varying
)"
有什么方法可以防止部分代码多次执行?
您可以在下面找到代码:
Is there any way to prevent part of the code from executing 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;
推荐答案
您可以使用 IF NOT EXISTS
子句以避免发生异常(在9.1版中引入):
You can use the IF NOT EXISTS
clause to avoid an exception (introduced with pg 9.1):
CREATE TEMPORARY TABLE IF NOT EXISTS tmpr (...);
在这种情况下,您最好检查表中是否有行:
You'd better check if there are rows in the table in this case:
IF EXISTS (SELECT 1 FROM tmpr) THEN -- table itself exists after above command
DELETE FROM tmpr;
END IF;
为避免后续调用该函数冲突,或者通常情况下,如果函数完成后不再需要临时表,请添加ON COMMIT DROP
:
To avoid that a subsequent call of the function conflicts, or generally, if you don't need the temp table any more after the function finishes, add ON COMMIT DROP
:
CREATE TEMPORARY TABLE IF NOT EXISTS tmpr (...) ON COMMIT DROP;
如果您在单笔交易中重复调用该函数,此操作仍将失败.在这种情况下,您可以在函数的末尾添加显式的DROP TABLE
语句.
This would still fail if you call the function repeatedly inside a single transaction. In this case, you can add explicit DROP TABLE
statements to the end of your function instead.
这篇关于函数中的临时表和循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!