Refer to Section 6.3 for information about record privileges and Section 17.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 |