Skip to content

Instantly share code, notes, and snippets.

@greenygh0st

greenygh0st/pg-chown.sql

Created September 30, 2022 02:03
Show Gist options
  • Save greenygh0st/61b271b1f9e6353e225d74e71f1ede5f to your computer and use it in GitHub Desktop.
Save greenygh0st/61b271b1f9e6353e225d74e71f1ede5f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. greenygh0st created this gist Sep 30, 2022.
    63 changes: 63 additions & 0 deletions pg-chown.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,63 @@

    -- taken from: https://dba.stackexchange.com/questions/171739/change-owner-of-all-schema-objects
    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$