ANBOB™

在数据库维护中有时需要使用SQL生成SQL用于执行，如DDL或Kill Session等场景， 从数据库字典和动态性能View拼成SQL TEXT然后执行，通常是可以使用“||”拼接， 有时还有可能在SQL文本中出现引号等特殊的字符需要转义，在Oracle中有 escape指转义符或”(2个单引号)表示'(单引号)，也可以使用”q”转义，在Postgresql系中还一些新玩法, 避免因引号或转义过多时影响了阅读性。

Oracle拼SQL

SQL> select 'what ''s your name? ok!' from dual;

'WHAT''SYOURNAME?OK!'
------------------------------------------------------------------
what 's your name? ok!

SQL> select q'! what 's your name? ok! !' from dual;

Q'!WHAT'SYOURNAME?OK!!'
------------------------------------------------------------------------
 what 's your name? ok!
--q  [], {}, (), and <>.

SQL> select 'alter table anbob.'||table_name||' add  flag char(1) default ''0''  ;' from dba_tables where owner='ANBOB';

'ALTERTABLEANBOB.'||TABLE_NAME||'ADDFLAGCHAR(1)DEFAULT''0'';'
-----------------------------------------------------------------------------------------------------------------
alter table anbob.TEST1 add  flag char(1) default '0'  ;
alter table anbob.TEST add  flag char(1) default '0'  ;


SQL> select 'alter table anbob.'||table_name||q'[ add  flag char(1) default '0'  ;]' from dba_tables where owner='ANBOB';

'ALTERTABLEANBOB.'||TABLE_NAME||Q'[ADDFLAGCHAR(1)DEFAULT'0';]'
--------------------------------------------------------------------------------------------
alter table anbob.TEST1 add  flag char(1) default '0'  ;
alter table anbob.TEST add  flag char(1) default '0'  ;

Posgtresql/openGauss拼SQL

CREATE TABLE TEST(ID INT);

select ' alter table '||schemaname||'.'||tablename|| ' add column flag text not null default ''0'' '
 as ddl from pg_tables where schemaname='public';
 ddl
-------------------------------------------------------------------
 alter table public.test add column flag text not null default '0'

select format(
$SQL$ alter table %1$8I.%2$-30I add column %3$I text not null default %4$L $SQL$ ,
schemaname, tablename, 'flag', '0'
) as ddl from pg_tables where schemaname='public';
ddl
-------------------------------------------------------------------------------------------------
alter table public.test add column flag text not null default '0'
(1 row)

当标识符包含特殊字符、保留词或区分大小写的词时，应使用双引号。 使用上面的拼接和oracle一样，但postgresql中的format()函数可以像其它开发语言一样更直观，像printf()一样使用占位符，也避免了像2个引号的转换，但需要用()括起来，另外在PostgreSQL有一个简单的解决方案：美元引用的字符串常量，其中两个美元符号（$）之间的也可以自定义标签或不带标签如$$, 像PLpg/SQL的DO语句块一样，标签可以匹配更加灵活。如下

# do $PL$ begin raise notice $MSG$ select I'm a quote $MSG$ ; end; $PL$;
NOTICE: select I'm a quote
DO 

# select format('alter table %I.%I add column', schemaname, tablename) from pg_tables where schemaname='public';
              format
----------------------------------
 alter table public.t1 add column
(1 row)

PostgreSQL 的FORMAT()

PostgreSQL 的FORMAT()函数的语法如下：
FORMAT(format_string [, format_arg [, …] ])
FORMAT()函数是可变参数的，参数列表是用VARIADIC关键字标记的，因此，您可以传递数组形式的参数进行格式化。

format_string由文本和格式说明符组成。文本直接复制到结果字符串，而格式说明符是要插入到结果字符串的参数的占位符。
下面显示了格式说明符的语法：
%[position][flags][width]type

type 参数的允许值如下：
s将参数值格式化为字符串。NULL 被视为空字符串。
I将参数值视为 SQL 标识符。
L将参数值用引号修饰 SQL 文字。
我们经常使用I和L来构造动态 SQL 语句。

MogDB=# select format('add column %s text','where') as cmd;
select format('add column %s text','where') as cmd;
          cmd
-----------------------
 add column where text
(1 row)


MogDB=# select format('add column %I text','where') as cmd;
select format('add column %I text','where') as cmd;
           cmd
-------------------------
 add column "where" text
(1 row)

MogDB=# select format('add column %L  text','where') as cmd;
select format('add column %L  test','where') as cmd;
           cmd
--------------------------
 add column 'where'  text

对于%I比%s格式更好，对于SQL标识符可以自动增加双引号。还可以使用配置左右空格补齐。还可以%n$复用参数占位。

MogDB=# select format ('I %1$s, you %1$s, we all %1$s for ice %2$s. ', 'scream','cream');
 select format ('I %1$s, you %1$s, we all %1$s for ice %2$s. ', 'scream','cream');
 format
 -----------------------------------------------------
 I scream, you scream, we all scream for ice cream.
 (1 row)

在Postgresql command还支持\gexec直接执行，但OPENGAUSS还不支持。

Summary:
在Postgresql中可以使用format()打印SQL, 使用$[tag]$ 匹配引号，还可以使用format type格式化文本对齐与标识自动加引号，创建易于维护的脚本，没有硬编码文本，并且不会忽略标识符的双引号。

打赏

微信扫一扫，打赏作者吧～