17.3. 更换纪录所有人

参见资料记录权限Section 6.3,以及特殊字段Section 19.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