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.

score 3 · Accepted Answer · answered Oct 03 '13 at 00:19

You could refine the loop some more (demonstrating only the second query):

DO
$do$
DECLARE
    r   record;
    sql 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'
        -- and other conditions, if needed
    LOOP
        sql := sql
          || format(E'\nALTER FUNCTION public.%I(%s) SET SCHEMA new_schema;'
                   ,r.proname, r.params);
    END LOOP;

    RAISE NOTICE '%', sql; -- for viewing the sql before executing it
    -- EXECUTE sql; -- for executing the sql
END
$do$;

Major points

Assignment operator in plpgsql is :=. = works, but is undocumented.
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 effective. One SELECT with string_agg() does the job:

DO
$do$
DECLARE
   sql text;
BEGIN
   SELECT INTO sql
          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 '%', sql; -- for viewing the sql before executing it
   -- EXECUTE sql; -- for executing the sql
END
$do$;

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 — Michael M, Oct 03 '13 at 18:09
BTW, +1 for mentioning the use of something that works but is not documented - it is better to do things the documented way. — Michael M, Oct 05 '13 at 00:05

Michael M · Answer 2 · 2013-10-03T18:06:10.173

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;$$;

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. — Michael M, Oct 02 '13 at 22:48

Change schema of multiple PostgreSQL functions in one operation?

2 Answers2

Major points

Better set-based alternative

Linked