Drop table if exists else proceed for next step in function using PostgreSQL

Question

I am trying to drop table if exists else proceed for next step in function using PostgreSQL 9.3 version.

Example:

  Create or replace function test(tablename varchar)
  returns setof record as
  $$
  Declare
        var1 varchar :='table_';
  Begin
        var1 :=var1 ||tablename;
        /* Here need to drop table if exists */
       drop table if exist var1;

       /* else proceed for next step */
       ....
       ....
   end;
   $$
   language plpgsql;

Answer 1

You need to run the DROP TABLE command as below using execute (As @FuzzyTree already pointed much before me)

execute 'drop table ' || var1;

(OR)

execute 'DROP VIEW ' || var1;

Also another pointer, DROP TABLE is not allowed in a non-volatile function. So you may have to change the last line of your function to be

LANGUAGE 'plpgsql' VOLATILE;

Check this post, Will get you better idea How to delete table *or* view from PostgreSQL database?

Answer 2

Try using EXECUTE

EXECUTE 'DROP TABLE IF EXISTS ' || var1;