CODEWITHSUNDEEP
postgresqlplpgsql
SABER
SABER

Reputation: 373

Dropping multiple columns based on their name?

I'm creating a procedure/function in PostgreSQL. I have an array containing some column name and a temporary table as follows;

columns_names varchar[] := array['A','B','C','D'];
table PQR(A integer, B integer, C integer, X integer, Y integer);

I want to drop columns X and Y(i.e columns which are not present given array).

Is there any way to achieve this in single line statement?

Something like 

alter table pqr drop column where columnName not in column_names

Upvotes: 1

Views: 425

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

You could do that if you are using function like you mentioned and language is set to plpgsql, then dynamic SQL is possible.

For example:

EXECUTE concat('ALTER TABLE ',
               attrelid::regclass::text, ' ',
               string_agg(concat('DROP COLUMN ', attname), ', ')
              )
   FROM pg_attribute
  WHERE attnum > 0
    AND NOT attisdropped
    AND attrelid = 'PQR'::regclass
    AND attname != ALL(array['A','B','C','D'])
  GROUP BY attrelid;

It will only work for one table, otherwise it will complain about returning more than one row. If you need more tables, then you can use LOOP and execute query in it.

Upvotes: 3

Related Questions