4

My purpose is assigning the name of the tables into an array and drop them in the foreach loop via this array.

I am actually trying to do something more complicated but before I try to get the following code working:

CREATE OR REPLACE FUNCTION delete_auto()
  RETURNS void AS
$BODY$DECLARE
t text;
tbl_array text[] = array["ID: 889197824 CH: 0006 Messdaten2","ID: 889197824 CH: 0006 Messdaten3","ID: 889197824 CH: 0006 Messdaten4"];
BEGIN 

FOREACH t IN ARRAY tbl_array LOOP
DROP TABLE t;
END LOOP;
END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION delete_auto()
  OWNER TO postgres;

Function seems to me pretty correct but it does not drop the tables, does nothing actually after I execute it. I just get such an error on the log:

Table »t« does not exist.

I have also tried the same code with varchar[] instead of text[] but not working either. Any help would be appreciated

Improve this question

2 Answers 2

Reset to default
15

There are a few errors:

  • you cannot use double quotes for string literals (it is used for case sensitive SQL identifiers)

  • you should use dynamic SQL for parametrized DDL statements (statement EXECUTE). DDL statements does not have execution plan and these statements doesn't support parametrization (you should not use variables there)

    DO $$
DECLARE
  tables varchar[] := ARRAY['t1','t2'];
  t varchar;
BEGIN
  FOREACH t IN ARRAY tables
  LOOP
    EXECUTE format('DROP TABLE %I', t);
  END LOOP;
END;
$$;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

what if I have to use 2 or 3 variables? t for %I, z for %Z and h for %H for example, should I have to add them according to the order they have used? If I use first %I then %H and then %Z should I add: EXECUTE format ('DROP TABLE %I WHERE %H = %Z',t,h,z) ?
format('DROP TABLE %I WHERE %I = %L', t, h, z) - postgresql.org/docs/9.2/static/functions-string.html
@mctuna: DROP TABLE WHERE .. ? You may be thinking of DELETE FROM tbl WHERE ....
@ErwinBrandstetter You are right, I gave it just as an example though
0

What is the version of plpgsql you are using? It is convenient if you add that piece of information whenever you ask something.

The syntax to drop a table is

drop table [if exists] **name** [CASCADE] [RESTRICT]

The value of t does not look like the name of a table.

Other possible explanations: Your function is supposed to return void, hence it is correct that it does not show anything. Do you mean that the tables are not removed? Have you tested it first using t and trying to make an insert on one of the tables to find out if the table you tried to remove exists? Is the person executing the function the owner of the tables?

Improve this answer

10 Comments

version is 9.2. I have tested it without foreach loop as DROP TABLE "name_of_table" and it worked as intended. Now I am trying to create an array put the table names there and using this array in the foreach loop in order to prevent writing the same code. And hence "t" is not the table name but representing the table name in the foreach loop
I understood that t was not the table name, but the placeholder. What I was trying to say was: do you really have tablenames equal to for instance ID: 889197824 CH: 0006 Messdaten2? I guess not. :-)
yes they are the correct names but on the log I have seen such an error: Table »t« does not exist. Foreach loop is built wrong somehow probably, placeholder is not functioning right
If I try to create a table with that name, then returns it an error, because afaik are only letters,digits and underscores allowed in names for postgresql. Hence no semicolons and spaces. It can start with a letter or underscore, not with a number.
Postgresql will think that the table name ends at the whitespace.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.