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

postgresql show tables, views, index, sequences

There are some alternatives to show tables in postgresql.

1) using information_schema.tables To show all tables in Database it’s possible to use :

SELECT *
 FROM information_schema.tables
 WHERE table_schema='public' and table_type='BASE TABLE'

to check if table exist

SELECT *
 FROM information_schema.tables
 WHERE table_schema='public' and table_type='BASE TABLE' and table_name='products'

2) using pg_catalog.pg_tables

SELECT * FROM pg_catalog.pg_tables

schema public

SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public'

3) a bit complex but show many objects. it’s possible to show using pg_class      tables, views, sequences, index, special :

  • table = WHERE c.relkind IN (‘r‘,”)
  • sequences = WHERE c.relkind IN (S‘,”)
  • view = WHERE c.relkind IN (‘v‘,”)
  • index = WHERE c.relkind IN (‘i‘,”)
  • special = WHERE c.relkind IN (s‘,”)
SELECT n.nspname as "Schema",
       c.relname as "Name",
       CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN ('r','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

4) draft using pg_class

SELECT relname FROM  pg_class WHERE relname ='r'  ;

Resources:

Risorse:

Postgresql 

Plpgsql 

Creare eliminare database e tabelle

Postgresql reset sequence

Consistenti modifiche in una tabella rendono necessario talvota resettare il valore
della sequence associata.

Reset sequences : examples query

SELECT  SETVAL(‘documents_itemid_seq’,
( SELECT MAX (itemid)
FROM documents))  WHERE ( SELECT MAX(itemid)  FROM documents) > 0;

select setval(‘events_itemid_seq’,  (select max(itemid) from events))  where(select max(itemid) from events) > 0;

Risorse:

Fixing Sequences – PostgreSQL wiki

Risorse:

Postgresql 

Plpgsql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle