Postgresql – Recursive calls in stored procedure and temporary tables

postgresqlrecursivestored-procedures

I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to which subsequent recursive calls generate error.

How should this be fixed so as to avoid error?
Also, how can I improve performance of this procedure?

Definition of Stored Procedure:

-- Function: ramerdouglaspeuckerfunction2(text, double precision, integer, integer)

-- DROP FUNCTION ramerdouglaspeuckerfunction2(text, double precision, integer, integer);

CREATE OR REPLACE FUNCTION ramerdouglaspeuckerfunction2(IN table_name text, IN epsilon double precision, IN startindex integer, IN endindex integer)
  RETURNS TABLE(up_link double precision, time_stamp timestamp without time zone) AS
$BODY$ 
declare dmax double precision default 0;
declare idx integer default 0;
declare a integer;
declare b double precision;
declare c double precision;
declare d double precision;
declare e double precision;
declare norm double precision;
declare distance double precision;
declare i integer default 2;
declare j double precision;
declare result result_load_status;
begin
create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
a := endIndex - startIndex;
execute 'select up_link from ' || table_name || ' where bar2 = '|| startIndex    into d;
execute 'select up_link from ' || table_name || ' where bar2 = '|| endIndex      into e;
b := e-d;
c := -(b * startIndex - a * d);
norm := sqrt(power(a, 2) + power(b, 2));

loop
execute 'select up_link from ' || table_name || 'where bar2=' || i into j;
distance := abs(b * i - a * j + c) / norm;
if distance > dmax
then  
idx := i;
dmax := distance;
end if;
i := i+1;
exit
when (i > endIndex);

end loop;

if (dmax >= epsilon) 
then
insert into test
select * from ramerDouglasPeuckerFunction2(table_name,epsilon, startIndex,idx);

insert into test
select * from ramerDouglasPeuckerFunction2(table_name,epsilon, idx, endIndex);

else
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || startIndex into result ;
execute 'insert into test values(' || result.load_status || ','|| result.time_stamp || ')';

execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || endIndex into result ;
execute 'insert into test values(' || result.load_status || ','|| result.time_stamp || ')';

end if;

return query select * from test;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION ramerdouglaspeuckerfunction2(text, double precision, integer, integer)
  OWNER TO postgres;

Error Message:

ERROR:  relation "test" already exists
CONTEXT:  SQL statement "create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop"
PL/pgSQL function ramerdouglaspeuckerfunction2(text,double precision,integer,integer) line 15 at SQL statement

Best Answer

Drop the table if it exists before the next run.

...
begin
drop table if exists test;
create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
a := endIndex - startIndex;
...

Or, as @dezso suggests, you could create the table only if it doesn't exist.

...
begin
create temp table IF NOT EXISTS test(up_link double precision, time_stamp timestamp without time zone) on commit drop;
TRUNCATE test;
a := endIndex - startIndex;
...

As far as efficiency goes, this function works about as well as it can (O=n). That is, each statement is executed a single time and the flow is pretty linear. It would take some knowledge of the underlying tables to make any significant improvements. Those improvements are likely to be measured in small percentages, not geometric or exponential.

Related Question