在一个操作中更改多个PostgreSQL函数的模式？

问题描述

最近我需要将对象从PostgreSQL的默认模式public移动到另一个模式。如果找到帖子，它显示了如何移动表格这很棒，但我也需要移动这些函数。 您可以再细化一下循环（演示只有第二个查询）：

  DO 
 $ do $ 
 DECLARE 
r记录; 
 sql text =''; 
 BEGIN 
 FOR r 
 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'
  - 以及其他条件，如果需要的话
 LOOP 
 sql：= sql 
 ||格式（E'\\\
ALTER FUNCTION public。％I（％s）SET SCHEMA new_schema;'
，r.proname，r.params）; 
 END LOOP; 
 
 RAISE NOTICE'％'，sql; - 在执行之前查看sql 
  -  EXECUTE sql; - 执行sql 
 END 
 $ do $; 
  

要点

plpgsql中的赋值运算符是：= 。 = 有效，但没有记录。 / code>。


• concat（）可能会矫枉过正，但 format（）简化了语法。


h2>



将问题重新转换为基于集合的操作更为有效。使用 string_agg（）执行一个SELECT操作：



  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'\\\
'）
 FROM pg_proc p 
 JOIN pg_namespace n ON n.oid = p.pronamespace 
 WHERE nspname ='public'; 
  - 以及其他条件（如果需要）
 
 RAISE通知'％'，sql; - 在执行之前查看sql 
  -  EXECUTE sql; - 执行sql 
 END 
 $ do $; 
  




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

解决方案

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

这篇关于在一个操作中更改多个PostgreSQL函数的模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！