2

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
  • 1
    How about moving the 'non-recursive' functionality (including the temp table creation) out to an other function and call the recursive magic from it? At the same time, some indentation in your code could help readers understanding what you try to achieve. At at still the same time, are you sure that floating point variables are good for this sort of calculation? – dezso Jun 3 '14 at 14:20
  • This might be a good candidate for PL/Python, PL/Perl, etc. Because PL/PgSQL uses the SPI and effectively runs each line as an SQL statement, it's really not fast for things like maths. Alternately, if you're really keen, see if you can express this as a recursive CTE ;-) – Craig Ringer Jun 4 '14 at 5:37
1

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.

  • Alternatively, one can simply check if the table exists and create it only if necessary. Of course, all depends on if the data inserted from the outer calls should persist for the inner calls or not. – dezso Jul 10 '14 at 21:01
  • @dezso added code to illustrate your suggestion. – Kirk Roybal Jul 10 '14 at 21:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.