WHERE NOT nsp.nspname IN ('pg_catalog', 'information_schema'))īased on the answer by elysch, here is a solution for multiple schemas: DO $$
JOIN pg_namespace nsp ON p.pronamespace = nsp.oid SELECT 'alter function '||nsp.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to newuser ' as command SELECT 'ALTER SCHEMA '|| schemaname ||' OWNER TO newuser ' as commandĪlso note the functions and other components of the database that may need to change memberships SELECT distinct(schemaname) FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO newuser ' as command SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO newuser ' as command
SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO newuser' as command If current owner is not postgres you can use this: REASSIGN OWNED BY old_role TO new_roleīut if the current owner is postgres you definitely get error so you have to use answer but if you want to execute commands in same sql use these command based on need:ĭatabase ALTER DATABASE target_database OWNER TO new_onwer Select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner Select 'ALTER SCHEMA "' || v_schema || '" OWNER TO ' || v_new_owner Select 'ALTER FUNCTION "'||nsp.nspname||'"."'||p.proname||'"('||pg_get_function_identity_arguments(p.oid)||') OWNER TO ' || v_new_owner || ' ' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = v_schemaīased on answers provided by and this answer by a lot.īetter yet: Also change database and schema owner. Select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ' ' as a from information_schema.views where table_schema = v_schema Select 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ' ' as a from information_quences where sequence_schema = v_schema Select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ' ' as a from information_schema.tables where table_schema = v_schema (Tested in PostgreSql v9.2) DO $$DECLARE r record I like this one since it modifies tables, views, sequences and functions owner of a certain schema in one go (in one sql statement), without creating a function and you can use it directly in PgAdmin III and psql:
This generates all the required ALTER TABLE / ALTER SEQUENCE / ALTER VIEW statements, copy these and paste them back into plsql to run them.Ĭheck your work in psql by doing: \dt *.* Views SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO my_new_owner 'įROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')īased on this answer SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner ' Sequences SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO my_new_owner 'įROM information_quences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema') Tables SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO my_new_owner 'įROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') This: is also a nice and fast solution, and works for multiple schemas in one database:
0 kommentar(er)
