Skip to content

Instantly share code, notes, and snippets.

@BacLuc

BacLuc/change-owner.sql

Created August 8, 2025 08:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save BacLuc/32ffa0c2af429459bf9cce9e98c39cda to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save BacLuc/32ffa0c2af429459bf9cce9e98c39cda to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. BacLuc created this gist Aug 8, 2025.
    42 changes: 42 additions & 0 deletions change-owner.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    DO $$ DECLARE
    r RECORD;
    target_user text := 'target-user';
    statement text;
    BEGIN
    -- table
    FOR r IN (
    SELECT tablename as entry
    FROM pg_tables
    WHERE
    schemaname = current_schema())
    LOOP
    statement := 'ALTER TABLE ' || quote_ident(r.entry) || ' OWNER TO ' || quote_ident(target_user);
    RAISE NOTICE 'statement: %', statement;
    EXECUTE statement;
    END LOOP;

    -- sequence
    FOR r IN (
    SELECT sequencename as entry
    FROM pg_sequences
    WHERE
    schemaname = current_schema())
    LOOP
    statement := 'ALTER SEQUENCE ' || quote_ident(r.entry) || ' OWNER TO ' || quote_ident(target_user);
    RAISE NOTICE 'statement: %', statement;
    EXECUTE statement;
    END LOOP;

    -- view
    FOR r IN (
    SELECT viewname as entry
    FROM pg_views
    WHERE
    schemaname = current_schema())
    LOOP
    statement := 'ALTER VIEW ' || quote_ident(r.entry) || ' OWNER TO ' || quote_ident(target_user);
    RAISE NOTICE 'statement: %', statement;
    EXECUTE statement;
    END LOOP;

    END $$;