Cambiare l’owner degli oggetti in un database postgresql: type, table, sequence, view, index,special, function

Una comoda query per modificare l’owner di tutti gli oggetti presenti in uno schema del database postreSQL.

La query è ripresa dal blog garysieling:  https://www.garysieling.com/blog/postgres-change-owner-all-objects-in-schema

Sono state apportate poche modifiche la principale per consentire l’utilizzo del maiuscolo nel nome degli oggetti e una diversa organizzazione degli oggetti type, table, sequence, index, view e special.

La query utilizza pg_class per visualizzare gli oggetti: tipo, tabella, sequenza, vista, indice e special.

Nel campo pg_class.relkind viene indicato il tipo di oggetto. Di seguito i codici presenti in relkind:

  • table ordinary = relkind = r
  • table TOAST  = t
  • table foreign = f
  • sequence = S
  • view = ‘v
  • index = ‘i
  • special = s
-- FUNCTION: public.__chown_object(character varying, character varying)

-- DROP FUNCTION public.__chown_object(character varying, character varying);

CREATE OR REPLACE FUNCTION public.__chown_object(
	in_schema character varying,
	new_owner character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    --ROWS 0
AS $BODY$

DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;
 
  r record;
BEGIN
  -- modifica owner tipi, tabelle, sequenze, indici e viste
  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 '
          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, object_type.code;
                     
             
     EXECUTE 
       'alter ' || object_type.type_name || ' '
                || r.nspname || '."' || r.relname 
                 || '" owner to ' || new_owner;
    END loop;
  END loop;
  -- end modifica owner tipi, tabelle, sequenze, indici e viste

  -- modifica owner funzioni 
  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; 
  -- modifica owner funzioni 

  -- modifica owner particolari 
  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;
  -- modifica owner particolari  -

END;

$BODY$;

ALTER FUNCTION public.__chown_object(character varying, character varying)
    OWNER TO postgresql;

Plpgsql 

Postgresql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...