PostgreSQL quote ident and literal
背景
在PostgreSQL中，支持特殊字符的表名，列名等，但是他们在使用时需要使用双引号，例如包含大小写的表名。
在字符串输入时，可以使用的方式引用字符串，也可以使用单引号，但是如果在字符串中出现了应用字符，需要对引用字符进行转义。
这是数据库操作的常见问题。
PostgreSQL 提供了几个函数用来输出转义后的字符串.
https://www.postgresql.org/docs/9.6/static/functions-string.html
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| quote_ident(string text) | text | Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. See also Example 41-1. | quote_ident(‘Foo bar’) | “Foo bar” |
| quote_literal(string text) | text | Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 41-1. | quote_literal(E’O'Reilly’) | ‘O’‘Reilly’ |
| quote_literal(value anyelement) | text | Coerce the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled. | quote_literal(42.5) | ‘42.5’ |
| quote_nullable(string text) | text | Return the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled. See also Example 41-1. | quote_nullable(NULL) | NULL |
| quote_nullable(value anyelement) | text | Coerce the given value to text and then quote it as a literal; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled. | quote_nullable(42.5) | ‘42.5’ |
一般可用于构造SQL语句, 例如dblink中的动态SQL语句的构造 :
http://blog.163.com/digoal@126/blog/static/16387704020128772037884/
http://blog.163.com/digoal@126/blog/static/163877040201321125220134/
http://blog.163.com/digoal@126/blog/static/1638770402012731944439/
防止因为输入问题造成的不正确SQL.
正文
quote_ident用于构造数据库对象名, 例如表名, 列名.
quote_literal用于构造字符串.
quote_nullable和quote_literal类似, 只是空参数的处理不一样.
quote_literal是strict的, 输入空, 返回空.
quote_nullable是not strict的, 内部处理空值, 返回NULL字符串.
digoal=# select proisstrict,provolatile,proname from pg_proc where proname ~ 'quote';
proisstrict | provolatile | proname
-------------+-------------+----------------
t | i | quote_ident
t | i | quote_literal
t | s | quote_literal
f | i | quote_nullable
f | s | quote_nullable
(5 rows)
src/backend/utils/adt/quote.c
/*
* quote_nullable -
* Returns a properly quoted literal, with null values returned
* as the text string 'NULL'.
*/
Datum
quote_nullable(PG_FUNCTION_ARGS)
{
if (PG_ARGISNULL(0))
PG_RETURN_TEXT_P(cstring_to_text("NULL"));
else
PG_RETURN_DATUM(DirectFunctionCall1(quote_literal,
PG_GETARG_DATUM(0)));
}
quote_nullable和quote_literal的差异 :
digoal=# select 1 where quote_nullable(null) is null;
?column?
----------
(0 rows)
digoal=# select 1 where quote_literal(null) is null;
?column?
----------
1
(1 row)
digoal=# select 1 where quote_literal(null)='NULL';
?column?
----------
(0 rows)
digoal=# select 1 where quote_nullable(null)='NULL'; -- 当输入为空时，返回'NULL'字符串
?column?
----------
1
(1 row)
构造动态SQL举例.
不使用quote :
digoal=# create or replace function f_test(i_tablename text, i_cname text, i_cval text) returns void as $$
declare
v_sql text;
begin
v_sql := 'create table '||i_tablename||'('||i_cname||' text)';
raise notice '%', v_sql;
execute v_sql;
v_sql := 'insert into '||i_tablename||'('||i_cname||') values ('||i_cval||')';
raise notice '%', v_sql;
execute v_sql;
end;
$$ language plpgsql;
CREATE FUNCTION
当表名, 列名中有空格或其他特殊字符时, 必须要用双引号, 因此以下调用将报错.
digoal=# select f_test('a b','d e','''');
NOTICE: 00000: create table a b(d e text)
LOCATION: exec_stmt_raise, pl_exec.c:2985
ERROR: 42601: syntax error at or near "b"
LINE 1: create table a b(d e text)
^
QUERY: create table a b(d e text)
CONTEXT: PL/pgSQL function f_test(text,text,text) line 7 at EXECUTE statement
LOCATION: scanner_yyerror, scan.l:1044
当字符串中使用单引号时, 也必须逃逸, 使用双单引号. 这里也会报错.
digoal=# select f_test('ab','de','''');
NOTICE: 00000: create table ab(de text)
LOCATION: exec_stmt_raise, pl_exec.c:2985
NOTICE: 00000: insert into ab(de) values (')
LOCATION: exec_stmt_raise, pl_exec.c:2985
ERROR: 42601: unterminated quoted string at or near "')"
LINE 1: insert into ab(de) values (')
^
QUERY: insert into ab(de) values (')
CONTEXT: PL/pgSQL function f_test(text,text,text) line 10 at EXECUTE statement
LOCATION: scanner_yyerror, scan.l:1044
使用quote :
使用quote函数后, 数据库会自动根据需要帮你加上双引号和其他逃逸.
digoal=# create or replace function f_quote(i_tablename text, i_cname text, i_cval text) returns void as $$
declare
v_sql text;
begin
v_sql := 'create table '||quote_ident(i_tablename)||'('||quote_ident(i_cname)||' text)';
raise notice '%', v_sql;
execute v_sql;
v_sql := 'insert into '||quote_ident(i_tablename)||'('||quote_ident(i_cname)||') values ('||quote_literal(i_cval)||')';
raise notice '%', v_sql;
execute v_sql;
end;
$$ language plpgsql;
digoal=# select f_quote('a b','d e','''');
NOTICE: 00000: create table "a b"("d e" text)
LOCATION: exec_stmt_raise, pl_exec.c:2985
NOTICE: 00000: insert into "a b"("d e") values ('''')
LOCATION: exec_stmt_raise, pl_exec.c:2985
f_quote
---------
(1 row)
digoal=# select * from "a b";
d e
-----
'
(1 row)
参考
1. http://www.postgresql.org/docs/devel/static/functions-string.html
2. http://blog.163.com/digoal@126/blog/static/16387704020128772037884/
3. http://blog.163.com/digoal@126/blog/static/163877040201321125220134/
4. http://blog.163.com/digoal@126/blog/static/1638770402012731944439/