16.3. Changing Records Owner

Refer to Section 6.3 for information about record privileges and Section 18.6.1.1 for information about special columns.

System users often feel inconvenient when they use PostERP the first time and are prohibitted from updating table rows because they are not the record owners of many table rows.

In order to overwites column f99 in all rows of all tables in posterp with value en, one can prepare a file /tmp/t.sql containing the following PostgreSQL statements:

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('en');
SET session_replication_role = DEFAULT; --Enable triggers.
DROP FUNCTION ReplaceRowOwner(TEXT);
COMMIT;
		

and execute the follow command:

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