什么是动态SQL
在执行PL/pgSQL函数或SQL时,有时需要生成动态命令,因为命令涉及不同表或数据类型,仅在运行时才能确定具体对象或值。这时就很适合使用动态SQL。这里不过多解释,看下实例就很好理解了。
生成动态SQL的几种方式
- 使用 || 进行拼接
#查看序列当前使用值并加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
- 使用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
- 使用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
- 在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
- 使用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)
- 使用匿名块执行
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)
- 通过自定义函数执行
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)
- 当然也可以通过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