更改所有架構對象的所有者

我想更改架構中所有對象的所有者。為此，我使用以下功能：

CREATE OR REPLACE FUNCTION chown(in_schema VARCHAR, new_owner VARCHAR) 
RETURNS void AS
$$
DECLARE
 object_types VARCHAR[];
 object_classes VARCHAR[];
 object_type record;

 r record;
BEGIN
 object_types = '{type,table,sequence,index,table,view}';
 object_classes = '{c,t,S,i,r,v}';

 FOR object_type IN 
     SELECT unnest(object_types) type_name, 
               unnest(object_classes) code
 loop
   FOR r IN 
     EXECUTE '
         select n.nspname, c.relname 
         from pg_class c, pg_namespace n 
         where n.oid = c.relnamespace 
           and nspname = ''' || in_schema || '''
           and relkind = ''' || object_type.code || ''''
   loop 
     raise notice 'Changing ownership of % %.% to %', 
                 object_type.type_name, 
                 r.nspname, r.relname, new_owner;
     EXECUTE 
       'alter ' || object_type.type_name || ' '
                || r.nspname || '.' || r.relname 
                || ' owner to ' || new_owner;
   END loop;
 END loop;

 FOR r IN 
   SELECT  p.proname, n.nspname,
      pg_catalog.pg_get_function_identity_arguments(p.oid) args
   FROM    pg_catalog.pg_namespace n
   JOIN    pg_catalog.pg_proc p
   ON      p.pronamespace = n.oid
   WHERE   n.nspname = in_schema
 LOOP
   raise notice 'Changing ownership of function %.%(%) to %', 
                r.nspname, r.proname, r.args, new_owner;
   EXECUTE 
      'alter function ' || r.nspname || '.' || r.proname ||
      '(' || r.args || ') owner to ' || new_owner;
 END LOOP;

 FOR r IN 
   SELECT * 
   FROM pg_catalog.pg_namespace n
   JOIN pg_catalog.pg_ts_dict d 
     ON d.dictnamespace = n.oid
   WHERE n.nspname = in_schema
 LOOP
   EXECUTE 
      'alter text search dictionary ' || r.nspname || '.' || r.dictname || 
      ' owner to ' || new_owner;
 END LOOP;
END;
$$
LANGUAGE plpgsql;

( https://www.garysieling.com/blog/postgres-change-owner-all-objects-in-schema )

不幸的是，在某些情況下，該功能無法按預期工作。

CREATE ROLE user_old LOGIN;
CREATE ROLE user_new LOGIN;

CREATE SCHEMA schema_a AUTHORIZATION user_old;

CREATE TABLE schema_a.test_a (
id serial NOT NULL,
CONSTRAINT test_a_pkey PRIMARY KEY (id)
);

ALTER TABLE schema_a.test_a OWNER TO user_old;

SELECT chown('schema_a', 'user_new')

NOTICE: Changing ownership of sequence schema_a.test_a_id_seq to user_new
ERROR: cannot change owner of sequence “test_a_id_seq”
DETAIL: Sequence “test_a_id_seq” is linked to table “test_a”.
KONTEXT: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

********** Error **********

ERROR: cannot change owner of sequence “test_a_id_seq”
SQL state: 0A000
Detail: Sequence “test_a_id_seq” is linked to table “test_a”.
Context: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

有誰知道如何解決這一問題？

為避免提及錯誤，請嘗試更改順序：

CREATE OR REPLACE FUNCTION public.chown(in_schema character varying, new_owner character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
 object_types VARCHAR[];
 object_classes VARCHAR[];
 object_type record;

 r record;
BEGIN
 object_types = '{type,table,table,sequence,index,view}';
 object_classes = '{c,t,r,S,i,v}';

 FOR object_type IN
     SELECT unnest(object_types) type_name,
               unnest(object_classes) code
 loop
   FOR r IN
     EXECUTE format('
         select n.nspname, c.relname
         from pg_class c, pg_namespace n
         where n.oid = c.relnamespace
           and nspname = %I
           and relkind = %L',in_schema,object_type.code)
   loop
     raise notice 'Changing ownership of % %.% to %',
                 object_type.type_name,
                 r.nspname, r.relname, new_owner;
     EXECUTE format(
       'alter %s %I.%I owner to %I'
       , object_type.type_name, r.nspname, r.relname,new_owner);
   END loop;
 END loop;

 FOR r IN
   SELECT  p.proname, n.nspname,
      pg_catalog.pg_get_function_identity_arguments(p.oid) args
   FROM    pg_catalog.pg_namespace n
   JOIN    pg_catalog.pg_proc p
   ON      p.pronamespace = n.oid
   WHERE   n.nspname = in_schema
 LOOP
   raise notice 'Changing ownership of function %.%(%) to %',
                r.nspname, r.proname, r.args, new_owner;
   EXECUTE format(
      'alter function %I.%I (%s) owner to %I', r.nspname, r.proname, r.args, new_owner);
 END LOOP;

 FOR r IN
   SELECT *
   FROM pg_catalog.pg_namespace n
   JOIN pg_catalog.pg_ts_dict d
     ON d.dictnamespace = n.oid
   WHERE n.nspname = in_schema
 LOOP
   EXECUTE format(
      'alter text search dictionary %I.%I owner to %I', r.nspname, r.dictname, new_owner );
 END LOOP;
END;
$function$

另請閱讀REASSIGN OWNED

這裡還有關於如果你徘徊為什麼我改變訂單的資訊：relkind

r = 普通表，i = 索引，S = 序列，v = 視圖，m = 物化視圖，c = 複合類型，t = TOAST 表，f = 外部表

引用自：https://dba.stackexchange.com/questions/171739