刪除具有給定名稱的所有列
我正在將 MSSQL 模式轉換為 PostgreSQL,並且在該模式中,大多數表都有一個名為
Timestamp
MSSQLtimestamp
數據類型的列,它是有效的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
清理列名。細節:我假設您知道
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();