Change schema of multiple PostgreSQL functions in one operation?

Question

Recently I needed to move objects from PostgreSQL's default schema "public" to another schema. I found this post which shows how to move tables which was great, but I also need to move the functions.

Cover

Answer 1

Postgres 12 or newer

ALTER ROUTINE to cover both FUNCTION and PROCEDURE (added with pg 12).

DO
$do$
DECLARE
   _qry text;
BEGIN
   SELECT INTO _qry
          string_agg(format('ALTER ROUTINE public.%I(%s) SET SCHEMA new_schema;'  -- !
                   , p.proname, pg_get_function_identity_arguments(p.oid)), E'\n')
   FROM   pg_proc p
   JOIN   pg_namespace n ON n.oid = p.pronamespace
   WHERE  nspname = 'public';
      --  more filters?

   RAISE NOTICE '%', _qry;  -- inspect query string before executing
   -- EXECUTE _qry;         -- un-comment to execute
END
$do$;

The rest still applies:

Postgres 11 or older (original answer)

Refine the loop some more (demonstrating only the second query):

DO
$do$
DECLARE
   r   record;
   _qry text = '';
BEGIN
   FOR r IN
      SELECT p.proname, pg_get_function_identity_arguments(p.oid) AS params
      FROM   pg_proc p
      JOIN   pg_namespace n ON n.oid = p.pronamespace
      WHERE  nspname = 'public'
      --  more filters?
   LOOP
      _qry := _qry || format(E'\nALTER FUNCTION public.%I(%s) SET SCHEMA new_schema;'
                           , r.proname, r.params);
   END LOOP;

   RAISE NOTICE '%', _qry;  -- inspect query string before executing
   -- EXECUTE _qry;         -- un-comment to execute
END
$do$;

Remove unneeded tables from FROM.

concat() may be overkill, but format() simplifies the syntax.

Better set-based alternative

Re-casting the problem as set-based operation is more elegant. One SELECT with string_agg() does the job:

DO
$do$
DECLARE
   _qry text;
BEGIN
   SELECT INTO _qry
          string_agg(format('ALTER FUNCTION public.%I(%s) SET SCHEMA new_schema;'
                   , p.proname, pg_get_function_identity_arguments(p.oid)), E'\n')
   FROM   pg_proc p
   JOIN   pg_namespace n ON n.oid = p.pronamespace
   WHERE  nspname = 'public';
      -- and other conditions, if needed

   RAISE NOTICE '%', _qry;  -- inspect query string before executing
   -- EXECUTE _qry;         -- un-comment to execute
END
$do$;

Verla

Oops, I forgot to remove those extraneous joins from another application of that query. The way you wrote it looks way better with the format function. I'll be using that from now on. Fantastic idea to string_agg into the sql variable. Thanks Erwin – Cover

BTW, +1 for mentioning the use of something that works but is not documented - it is better to do things the documented way. – Cover

Consider pg_proc.prokind (f for function, p for procedure); you may want to ALTER PROCEDURE for the latter. – Supersonic

@AaronWest: The answer predates the addition of SQL procedures. I updated now with ALTER ROUTINE to cover both. – Verla

Answer 2

DO$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' -- and other conditions, if needed
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA [new_schema];';
    END LOOP;
END;
$$;

DO$$
DECLARE
    row record;
    sql text = E'\n';
BEGIN
    FOR row IN
        SELECT
               proname::text as proname,
               pg_get_function_identity_arguments(p.oid) AS params
        FROM pg_proc p
        JOIN pg_namespace n on n.oid = p.pronamespace
        WHERE nspname = 'public'
     -- and other conditions, if needed
    LOOP
        sql = CONCAT(sql, E'\n',
            'ALTER FUNCTION public.', row.proname,
            '(', row.params, ') SET SCHEMA [new_schema];');
    END LOOP;
    RAISE NOTICE '%', sql; -- for viewing the sql before executing it
    -- EXECUTE sql; -- for executing the sql
END;$$;

Cover

I think this is the best way to do it. If someone else has a better way, I would be glad to mark that as the answer instead. – Cover