寻找一种从Postgres的多个表中删除多列的方法

Question

我有大约300个表，每个表都在处理过程中添加了7列。这些列在每个表中的名称是相同的。我们不再使用这些列，必须删除它们。有没有办法从我批量指定的所有表中删除这些列？

Answer 1

您可以采用散弹枪的方法，并执行以下操作：

CREATE FUNCTION alter_tables() RETURNS VOID AS $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN SELECT table_name::TEXT FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = '<SCHEMA_NAME>' LOOP
        EXECUTE 'ALTER TABLE ' || table_record.table_name || ' DROP COLUMN IF EXISTS <COLUMN_NAME> CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT alter_tables();
DROP FUNCTION alter_tables();

根据需要更换<SCHEMA_NAME>和<COLUMN_NAME>。

如果您需要更多地选择要更改的表的确切集合，那么这(显然)将不起作用。

Answer 2

使用视图集执行此任务。您对视图特别感兴趣。示例代码可能如下所示：

-- test DDL
CREATE TABLE table1 (col1 INTEGER,col2 INTEGER);
CREATE TABLE table2 (col1 INTEGER,col2 INTEGER);

-- possible function definition
CREATE OR REPLACE FUNCTION delete_columns(schema_to_process TEXT,column_list TEXT[]) RETURNS VOID AS
$BODY$
DECLARE
  column_record RECORD;
BEGIN
  FOR column_record IN
    SELECT * FROM information_schema.columns AS cinfo
    WHERE cinfo.table_schema = $1
    AND cinfo.column_name = ANY($2)
  LOOP
    EXECUTE 'ALTER TABLE ' || $1 || '.' || column_record.table_name || ' DROP COLUMN IF EXISTS ' || column_record.column_name || ' CASCADE;';
  END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';

-- sample run
SELECT delete_columns('public',ARRAY['col2']);