Temporary table and loops in a function

Question

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;

Erwin Brandstetter · Answer 1 · 2012-10-22T21:04:05.263

2

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;

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;

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.

edited Oct 22 '12 at 21:04

answered Oct 22 '12 at 20:51

Erwin Brandstetter

605,456
145
1,078
1,228

BUT IT STILL POPS UP THE ERROR. IT JUST IGNORE IT AND CONTINUE. – IT_info Oct 22 '12 at 21:03
3

@Ryan: Your caps lock key seems broken. PostgreSQL does *not* continue after an error (exception). What you get is a (harmless) `NOTICE`, it the table already exists. – Erwin Brandstetter Oct 22 '12 at 21:05
For the second part of your answer, would dropping the tables explicitly at the end of the function also dodge the exception? Unfortunately, I am stuck with Postgres 8.4, and this is a rare scenario where I actually believe I need a temporary table and would like RAISE NOTICE statements (since highly iterative) so want to avoid dynamic EXECUTE – Jun 08 '15 at 20:53
@smaccoun: You can always drop tables explicitly instead of `ON COMMIT DROP` - which is basically a convenience feature. – Erwin Brandstetter Jun 09 '15 at 10:20
@edwin thanks, got it but was just curious if that solves the exception problem. In my particular use case it didn't appear too, so I ended up converting everything to dynamic SQL – Jun 19 '15 at 02:26

Temporary table and loops in a function

1 Answers1

Linked