plpgsql text[] varchar[] array not working

arrays postgresql plpgsql postgresql-9.2

29,422

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;
$$;
```

29,422

Author by

mctuna

Updated on July 09, 2022

Comments

mctuna almost 2 years
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
mctuna almost 11 years

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
Loek Bergman almost 11 years

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. :-)
mctuna almost 11 years

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
Loek Bergman almost 11 years

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.
Loek Bergman almost 11 years

Postgresql will think that the table name ends at the whitespace.
mctuna almost 11 years

Those are the tables which store the temperatures of the thermometers and they are inserted automatically. There are also someother odds like timestamps are all in "double precision" and we have to convert them to "timestamp with timezone" with function = ) But I use those names with aphostrop (of course) inside queries and till now I didnt have any problem
Loek Bergman almost 11 years

if you copy the content of one of the tables to a new table, which has a name within the format of postgresql as described above and next you try your function. If it works, then will you know it is the name. If it does not work, then will you know something has to be done with the function. By the way, I always use CASCADE to remove dependencies on the fly. You can add that anyhow.
mctuna almost 11 years

I have copied the content to a new table named "Messdaten" and changed the array accordingly but no working too
Loek Bergman almost 11 years

can you drop the table Messdaten using a function owned by postgres? Are the tables in the default schema public? The code looks ok to me. You can always make use of raise notice statements to see what you are doing exactly.
mctuna almost 11 years

I have managed to drop the table with a function owned by postgres but only difference it was a sql function not plpgsql. I have just written DROP TABLE "Messdaten" and it worked
mctuna almost 11 years

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) ?
Pavel Stehule almost 11 years

format('DROP TABLE %I WHERE %I = %L', t, h, z) - postgresql.org/docs/9.2/static/functions-string.html
Erwin Brandstetter almost 11 years

@mctuna: DROP TABLE WHERE .. ? You may be thinking of DELETE FROM tbl WHERE ....
mctuna almost 11 years

@ErwinBrandstetter You are right, I gave it just as an example though