Postgresql中如何执行动态DDL语句

什么是动态SQL

在执行PL/pgSQL函数或SQL时，有时需要生成动态命令，因为命令涉及不同表或数据类型，仅在运行时才能确定具体对象或值。这时就很适合使用动态SQL。这里不过多解释，看下实例就很好理解了。

生成动态SQL的几种方式

1. 使用 || 进行拼接

#查看序列当前使用值并加1
postgres=# select 'ALTER SEQUENCE '||sequencename||' RESTART '||last_value+1 from pg_sequences where last_value is not null;
                 ?column?                 
------------------------------------------
 ALTER SEQUENCE tb3_a_seq RESTART 4
 ALTER SEQUENCE products_id_seq RESTART 3
 ALTER SEQUENCE accounts_id_seq RESTART 6

2. 使用format函数

postgres=# select format('ALTER SEQUENCE %s  RESTART %s',sequencename,last_value + 1) from pg_sequences where last_value is not null;
                  format                   
-------------------------------------------
 ALTER SEQUENCE tb3_a_seq  RESTART 4
 ALTER SEQUENCE products_id_seq  RESTART 3
 ALTER SEQUENCE accounts_id_seq  RESTART 6

3. 使用quote_ident和quote_literal，使用这两个函数可以自动根据需要给相关的值转义，并加上单引号或者双引号。
   quote_ident用于构造数据库对象名, 例如表名, 列名
   quote_literal用于构造字符串,比如插入或者更新的值

#quote_ident和quote_literal对应format中的%I和%L
postgres=#  select 'ALTER SEQUENCE '||quote_ident(sequencename)||' RESTART '||last_value+1 from pg_sequences where last_value is not null;
                  ?column?                  
--------------------------------------------
 ALTER SEQUENCE tb3_a_seq RESTART 4
 ALTER SEQUENCE products_id_seq RESTART 3
 ALTER SEQUENCE accounts_id_seq RESTART 6

如何执行动态DDL

上面介绍了如何写动态SQL，下面进入今天的重点，看看如何自动执行动态DDL

1. 在psql中，可以通过变量引入执行

#tb3_a_seq是一个序列
postgres=# select * from tb3_a_seq \gset 
postgres=# select :last_value;
 ?column? 
----------
        3
postgres=# alter sequence tb3_a_seq restart :last_value;
ALTER SEQUENCE

2. 使用psql中的\gexec 命令执行

#拼接动态DDL
postgres=#  select 'ALTER SEQUENCE '||sequencename||' RESTART '||last_value+1 from pg_sequences where last_value is not null;
                 ?column?                 
------------------------------------------
 ALTER SEQUENCE tb3_a_seq RESTART 5
 ALTER SEQUENCE products_id_seq RESTART 4
 ALTER SEQUENCE accounts_id_seq RESTART 7
(3 rows)

#执行动态DDL
postgres=#  select 'ALTER SEQUENCE '||sequencename||' RESTART '||last_value+1 from pg_sequences where last_value is not null \gexec
ALTER SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE


#使用quote函数一样的效果
postgres=# select nextval('tb3_a_seq');
 nextval 
---------
      21
(1 row)
postgres=# select 'ALTER SEQUENCE '||quote_ident(sequencename)||' RESTART '||last_value+5 from pg_sequences where last_value is not null \gexec
ALTER SEQUENCE
postgres=#  select nextval('tb3_a_seq');
 nextval 
---------
      26
(1 row)

3. 使用匿名块执行

postgres=# select nextval('tb3_a_seq');
 nextval 
---------
       7
(1 row)
DO $$
declare
seqname varchar;
my_value bigint;
BEGIN
for seqname,my_value in select sequencename, last_value+1 from pg_sequences where last_value is not null
	loop
    EXECUTE format('ALTER SEQUENCE %s  RESTART %s;',seqname,my_value);
    end loop;
END
$$
LANGUAGE plpgsql;
#执行完成
DO
#查看序列值已增加1
postgres=# select nextval('tb3_a_seq');
 nextval 
---------
       8
(1 row)

4. 通过自定义函数执行

postgres=# select nextval('tb3_a_seq');
 nextval 
---------
      10
(1 row)

CREATE OR REPLACE FUNCTION exec(raw_query text) RETURNS text AS $$
BEGIN
  EXECUTE raw_query;
  RETURN raw_query;
END
$$
LANGUAGE plpgsql;

#方便观察起见，我们last_value加5
select exec(format('ALTER SEQUENCE %s  RESTART %s',(select sequencename from pg_sequences where last_value is not null),(select last_value+5 from pg_sequences where last_value is not null)));

postgres=# select nextval('tb3_a_seq');
 nextval 
---------
      15
(1 row)

5. 当然也可以通过format和\gexec命令执行

postgres=# select nextval('tb3_a_seq');
 nextval 
---------
      16
(1 row)

postgres=# select format('ALTER SEQUENCE %s  RESTART %s',sequencename,last_value + 5) from pg_sequences where last_value is not null \gexec
ALTER SEQUENCE
postgres=# select nextval('tb3_a_seq');
 nextval 
---------
      21
(1 row)

参考：
https://www.postgresql.org/docs/14/functions-string.html#FUNCTIONS-STRING-FORMAT
https://blog.crunchydata.com/blog/dynamic-ddl-in-postgresql