Postgresql实现动态SQL语句
本⽂介绍Postgresql如何实现动态SQL语句。
1. 动态SQL
动态SQL在程序启动时会根据输⼊参数替换相应变量。使⽤动态SQL可以创建更强⼤和灵活的应⽤程序，但在编译时SQL语句的全⽂不确定，因此运⾏时编译会牺牲⼀些性能。动态SQL可以是代码或SQL语句的⼀部分，动态部分要么由开发⼈员输⼊，要么由程序本⾝创建。
1.1 动态SQL使⽤场景
在PL/pgSQL函数或过程中有时需要⽣成动态命令，因为命令涉及不同表或数据类型，仅在运⾏时才能确定具体对象或值。这时⽐较适合使⽤动态SQL。
另外，在特定情况下，如果静态SQL语句⽆法执⾏，或者您真的不知道函数或过程要执⾏的确切SQL语句，那么您必须使⽤动态SQL。
1.2 动态SQL VS 静态SQL
动态SQL静态SQL
SQL语句在编译时编译SQL语句在运⾏时编译
SQL语句在编译时进⾏解析，有效性检查表、视图和过程。编译时优化并⽣成应⽤程序执⾏计划SQL语句在运⾏时进⾏编译解析SQL语句，有效性检查表、视图和过程，优化并⽣成应⽤程序执⾏计划
性能好，速度快与静态SQL相⽐较性能不好
不够灵活⾮常灵活
2. ⽣成动态SQL
共有三种⽅式实现动态SQL。使⽤format函数，使⽤quote_ident 和 quote_literal函数，使⽤连接操作符||(发⾳pipe)，我最喜欢使⽤第⼀种，下⾯分别进⾏说明。
注意：构建查询时插⼊的动态值需要仔细处理，因为可能需要包含引号。
2.1 使⽤format函数
⾸先我们介绍下format函数的形式参数：
%s s格式化参数值作为简单字符串.
%I I 处理参数值作为SQL 标识符，有必要增加双引号.
%L L 引⽤参数作为SQL字⾯值.
这⾥先要区分两个概念：SQL标识符和SQL字⾯值。
SQL标识符表⽰数据库名称、表名、索引名、schema名、约束名、游标名、触发器、列、视图名称。在动态SQL中，I%会按照SQL标识符进⾏解析。
SQL字⾯值表⽰显⽰值，数值、字符、字符串、布尔值，不代表SQL标识符。有不同类型的字⾯值：
类型举例
字符串 String‘Hello! everyone’
整数 Integer45, 78, +89 , -465,6E5
数值 Decimal45.56
⽇期 DateTime‘5/20/2020’ , TIMESTAMP ‘2020-05-20 12:01:01’;
字符 Character A’ ‘%’ ‘9’ ’ ’ ‘z’ ‘(’
布尔 Boolean true, false, null
下⾯举例说明：
SELECT format('Hello! Welcome to my %s!', 'Blog') as msg;
返回：Hello! Welcome to my Blog!
SELECT format('%s! Welcome to my %s! - %s','Hi','Blog','Ourtechroom') as msg;
返回 ：Hi! Welcome to my Blog! - Ourtechroom
SELECT format('INSERT INTO %I VALUES(%L)', 'tbl_test', 'test');
返回：INSERT INTO tbl_test VALUES('test')
这⾥%I 被替换为tbl_test，%L被替换为'test'
2.2 使⽤quote_indent 函数
PostgreSQL 的quote_indent 函数
quote_ident('Hello World');  // "Hello World" 字⾯量增加引号
quote_ident('mytable');  // mytable 表名称⾃动去掉引号
quote_ident('MyTable');  // MyTable 区分⼤⼩写，表名称⾃动去掉引号
除此之外还有⼏个类似函数。 QUOTE_LITERAL(string text), QUOTE_LITERAL(value anyelement), QUOTE_NULLABLE(value anyelement)；
QUOTE_LITERAL函数返回值⾃动增加引号。QUOTE_NULLABLE对于⾮空参数增加引号，否则返回null。
2.3 使⽤连接操作符||
举例：
select ' be.id from ' || tbl_name || ' t where mmodity_id = ' || sorttype  || ' order by t.amount ' ||  test_id  ||;
这种⽅式对于需要增加单引号⽐较⿇烦，且容易造成SQL注⼊。
3. 总结
本⽂介绍Postgresql三种⽅式实现动态SQL语句，并通过⽰例对⽐不同⽅式的差异。相⽐使⽤format⽅式更简单⾼效。
>sql语句替换表中内容

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。