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;

does drop table works in your Function ?

Vivek S.
– Vivek S.

2014-06-14 09:07:14 +00:00
Commented Jun 14, 2014 at 9:07 — Vivek S.
– Vivek S., Commented Jun 14, 2014 at 9:07

FuzzyTree · Accepted Answer · 2014-06-14 07:57:50Z

2

Try using EXECUTE

EXECUTE 'DROP TABLE IF EXISTS ' || var1;

edited Jun 14, 2014 at 7:57

answered Jun 14, 2014 at 7:43

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sarfaraz Makandar Over a year ago

Made same change. But getting only notification that table does not exists not proceeding for next procedure.

FuzzyTree Over a year ago

@Meem the issue is likely in your next procedure then because IF EXISTS is supposed to show a notification if a table does not exist, so there is nothing wrong with seeing a notification

Sarfaraz Makandar Over a year ago

Yes! I got it. Thank you so much.

Community · Accepted Answer · 2017-05-23 12:21:26Z

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?

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Jun 14, 2014 at 7:52

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

3 Comments

Sarfaraz Makandar Over a year ago

I did. But not proceeding for next step only giving notifying that table does not exists.

Sarfaraz Makandar Over a year ago

No! I have used it like "drop table if exists".

Rahul Over a year ago

@Meem, You will have to use IF .. ELSE construct to conditionally do the dropping. check the link I have mentioned in answer. that will get you what you are trying to do.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Linked

Related