15.3. 更換紀錄所有人

參見資料記錄權限Section 6.3,以及特殊欄位Section 17.6.1.1

PostERP在許多資料表欄位f99欄位中多填有紀錄(row)擁有者的帳號。初次使用PostERP時,常因為不是記錄擁有者而不被允許修改某些記記錄。

初次使用PostERP時,可以把下面的PostgreSQL指令儲存在檔案/tmp/t.sql

BEGIN;

CREATE FUNCTION ReplaceRowOwner(RowOwner TEXT) RETURNS VOID AS $$
DECLARE
	rec RECORD;
BEGIN
	FOR rec IN
	SELECT c.relname AS table,c.relhastriggers AS HasTriggers
	FROM pg_class c
		 LEFT JOIN pg_user u ON u.usesysid = c.relowner
		 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
		 JOIN pg_attribute a ON a.attrelid = c.oid
	WHERE c.relkind = 'r'
		AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
		AND pg_table_is_visible(c.oid)
		AND a.attnum > 0 AND NOT a.attisdropped
		AND a.attname = 'f99'
	LOOP
		IF rec.HasTriggers THEN
			EXECUTE format('ALTER TABLE %I DISABLE TRIGGER ALL',rec.table);
		END IF;
		EXECUTE format('UPDATE %I SET f99=%L WHERE f99 IS DISTINCT FROM %L',rec.table,RowOwner,RowOwner);
		IF rec.HasTriggers THEN
			EXECUTE format('ALTER TABLE %I ENABLE TRIGGER ALL',rec.table);
		END IF;
		RAISE NOTICE '%',rec.table;
	END LOOP;
END $$ LANGUAGE PLPGSQL VOLATILE;

SET session_replication_role = replica; --Disable triggers.
SELECT ReplaceRowOwner('tw');
SET session_replication_role = DEFAULT; --Enable triggers.
DROP FUNCTION ReplaceRowOwner(TEXT);
COMMIT;
		

然後執行這個指令,把資料庫posterp的每一個資料表的每一筆記錄的f99欄位更換成tw這個值:

psql -U postgres -f /tmp/t.sql posterp