刪除具有給定名稱的所有列

我正在將 MSSQL 模式轉換為 PostgreSQL，並且在該模式中，大多數表都有一個名為TimestampMSSQLtimestamp數據類型的列，它是有效的rowversion。

在將記錄插入這些表時，您不需要為時間戳列指定值，因為 MSSQL 會自動更新該列。我相信這與PG中的相同xmin。

所以現在當我的應用程序嘗試插入表時，PG 會爆炸說它需要該Timestamp列的值。

因此，我正在考慮在導入模式後編寫一些東西，從我的表中刪除所有被呼叫Timestamp並具有timestamp數據類型的列。

我發現我可以從中獲取列和表資訊pgclass，pgattribute但是我現在卡住瞭如何循環並進行檢查然後刪除列。有人可以指出我正確的方向嗎？

select
   t.relname,
   a.attname,
   d.typname
from
 pg_class t
 INNER JOIN pg_attribute a 
 on a.attrelid = t.oid
 INNER JOIN pg_type d
 on d.oid = a.atttypid
 where relkind='r' and attname = 'timestamp' and d.typname = 'timestamp'
ORDER BY t.relname

對於一次性使用，您不需要持久化函式。使用DO聲明：

DO
$do$
DECLARE
  rec record;
BEGIN
FOR rec IN
  SELECT a.attrelid::regclass::text AS tbl, a.attname
  FROM   pg_class c
  JOIN   pg_attribute a ON a.attrelid = c.oid
  WHERE  c.relkind = 'r'
  AND    a.attname = 'timestamp'
  AND    a.atttypid = 'timestamp'::regtype
  AND    a.attnum > 0
  AND    NOT a.attisdropped
LOOP
  RAISE NOTICE '%', format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
  -- Check test output before uncommenting EXECUTE!
  -- EXECUTE format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
END LOOP;
END
$do$;

要點

• 如頂部所述，可能不需要函式。DO語句的主體是相同的，預設語言是 plpgsql。

• 使用**FOR循環**的隱式游標。更簡單、更快捷。在 plpgsql 中很少需要顯式游標。

• 表名在 Postgres 數據庫中**不是唯一的。**在多個模式中可以有任意數量的同名表。

• 避免使用非標準（雙引號）標識符的SQL 注入和基本異常。

  • 轉換為regclass處理表名。同時，表名在必要時自動進行模式限定（考慮目前search_path）。

  • format()with%I清理列名。細節：

  • Postgres 函式中的 SQL 注入與準備好的查詢

• 我假設您知道timestamp和timstamptz數據類型？這只會刪除數據類型為 的列timestamp [without time zone]。

• 用 標識數據類型a.atttypid = 'timestamp'::regtype，因此您不必加入pg_type。更簡單。

• 排除系統列和死列：

AND a.attnum > 0 AND NOT a.attisdroppedand

好吧，我似乎讓它像這樣工作：

DROP FUNCTION somefuncname();
CREATE OR REPLACE FUNCTION somefuncname() RETURNS void LANGUAGE plpgsql AS $$
DECLARE
 tablename VARCHAR(500);
 columnname varchar(500);

 curs1 CURSOR FOR  select
   t.relname,
   a.attname,
   d.typname
   from
   pg_class t
   INNER JOIN pg_attribute a 
   on a.attrelid = t.oid
   INNER JOIN pg_type d
   on d.oid = a.atttypid
   where relkind='r' and attname = 'timestamp' and d.typname = 'timestamp'
   ORDER BY t.relname;

BEGIN
 FOR myrecordvar IN curs1 LOOP 
   RAISE NOTICE E'Table Name:%',myrecordvar.relname;
   EXECUTE 'ALTER TABLE "' || myrecordvar.relname || '" DROP COLUMN ' || myrecordvar.attname;
 END LOOP;
END
$$;
SELECT somefuncname();

引用自：https://dba.stackexchange.com/questions/75573