Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7de8112

Browse files
committed
Create a function quote_nullable(), which works the same as quote_literal()
except that it returns the string 'NULL', rather than a SQL null, when calledwith a null argument. This is often a much more useful behavior forconstructing dynamic queries. Add more discussion to the documentationabout how to use these functions.Brendan Jurd
1 parent40a3dfb commit7de8112

File tree

6 files changed

+134
-27
lines changed

6 files changed

+134
-27
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 35 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.424 2008/03/10 12:39:22 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.425 2008/03/23 00:24:19 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -1262,6 +1262,9 @@
12621262
<indexterm>
12631263
<primary>quote_literal</primary>
12641264
</indexterm>
1265+
<indexterm>
1266+
<primary>quote_nullable</primary>
1267+
</indexterm>
12651268
<indexterm>
12661269
<primary>repeat</primary>
12671270
</indexterm>
@@ -1523,6 +1526,7 @@
15231526
Quotes are added only if necessary (i.e., if the string contains
15241527
non-identifier characters or would be case-folded).
15251528
Embedded quotes are properly doubled.
1529+
See also <xref linkend="plpgsql-quote-literal-example">.
15261530
</entry>
15271531
<entry><literal>quote_ident('Foo bar')</literal></entry>
15281532
<entry><literal>"Foo bar"</literal></entry>
@@ -1535,6 +1539,10 @@
15351539
Return the given string suitably quoted to be used as a string literal
15361540
in an <acronym>SQL</acronym> statement string.
15371541
Embedded single-quotes and backslashes are properly doubled.
1542+
Note that <function>quote_literal</function> returns null on null
1543+
input; if the argument might be null,
1544+
<function>quote_nullable</function> is often more suitable.
1545+
See also <xref linkend="plpgsql-quote-literal-example">.
15381546
</entry>
15391547
<entry><literal>quote_literal('O\'Reilly')</literal></entry>
15401548
<entry><literal>'O''Reilly'</literal></entry>
@@ -1551,6 +1559,32 @@
15511559
<entry><literal>'42.5'</literal></entry>
15521560
</row>
15531561

1562+
<row>
1563+
<entry><literal><function>quote_nullable</function>(<parameter>string</parameter> <type>text</type>)</literal></entry>
1564+
<entry><type>text</type></entry>
1565+
<entry>
1566+
Return the given string suitably quoted to be used as a string literal
1567+
in an <acronym>SQL</acronym> statement string; or, if the argument
1568+
is null, return <literal>NULL</>.
1569+
Embedded single-quotes and backslashes are properly doubled.
1570+
See also <xref linkend="plpgsql-quote-literal-example">.
1571+
</entry>
1572+
<entry><literal>quote_nullable(NULL)</literal></entry>
1573+
<entry><literal>NULL</literal></entry>
1574+
</row>
1575+
1576+
<row>
1577+
<entry><literal><function>quote_nullable</function>(<parameter>value</parameter> <type>anyelement</type>)</literal></entry>
1578+
<entry><type>text</type></entry>
1579+
<entry>
1580+
Coerce the given value to text and then quote it as a literal;
1581+
or, if the argument is null, return <literal>NULL</>.
1582+
Embedded single-quotes and backslashes are properly doubled.
1583+
</entry>
1584+
<entry><literal>quote_nullable(42.5)</literal></entry>
1585+
<entry><literal>'42.5'</literal></entry>
1586+
</row>
1587+
15541588
<row>
15551589
<entry><literal><function>regexp_matches</function>(<parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> [, <parameter>flags</parameter> <type>text</type>])</literal></entry>
15561590
<entry><type>setof text[]</type></entry>

‎doc/src/sgml/plpgsql.sgml‎

Lines changed: 73 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.123 2008/01/2302:04:47 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.124 2008/03/2300:24:19 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1066,6 +1066,24 @@ EXECUTE <replaceable class="command">command-string</replaceable> <optional> INT
10661066
</para>
10671067
</note>
10681068

1069+
<example id="plpgsql-quote-literal-example">
1070+
<title>Quoting values in dynamic queries</title>
1071+
1072+
<indexterm>
1073+
<primary>quote_ident</primary>
1074+
<secondary>use in PL/PgSQL</secondary>
1075+
</indexterm>
1076+
1077+
<indexterm>
1078+
<primary>quote_literal</primary>
1079+
<secondary>use in PL/PgSQL</secondary>
1080+
</indexterm>
1081+
1082+
<indexterm>
1083+
<primary>quote_nullable</primary>
1084+
<secondary>use in PL/PgSQL</secondary>
1085+
</indexterm>
1086+
10691087
<para>
10701088
When working with dynamic commands you will often have to handle escaping
10711089
of single quotes. The recommended method for quoting fixed text in your
@@ -1091,32 +1109,64 @@ EXECUTE 'UPDATE tbl SET '
10911109
</programlisting>
10921110
</para>
10931111

1094-
<indexterm>
1095-
<primary>quote_ident</primary>
1096-
<secondary>use in PL/PgSQL</secondary>
1097-
</indexterm>
1098-
1099-
<indexterm>
1100-
<primary>quote_literal</primary>
1101-
<secondary>use in PL/PgSQL</secondary>
1102-
</indexterm>
1103-
11041112
<para>
11051113
This example demonstrates the use of the
11061114
<function>quote_ident</function> and
1107-
<function>quote_literal</function> functions. For safety,
1108-
expressions containing column and table identifiers should be
1109-
passed to <function>quote_ident</function>. Expressions containing
1110-
values that should be literal strings in the constructed command
1111-
should be passed to <function>quote_literal</function>. Both
1112-
take the appropriate steps to return the input text enclosed in
1113-
double or single quotes respectively, with any embedded special
1114-
characters properly escaped.
1115+
<function>quote_literal</function> functions (see <xref
1116+
linkend="functions-string">). For safety, expressions containing column
1117+
or table identifiers should be passed through
1118+
<function>quote_ident</function> before insertion in a dynamic query.
1119+
Expressions containing values that should be literal strings in the
1120+
constructed command should be passed through <function>quote_literal</>.
1121+
These functions take the appropriate steps to return the input text
1122+
enclosed in double or single quotes respectively, with any embedded
1123+
special characters properly escaped.
1124+
</para>
1125+
1126+
<para>
1127+
Because <function>quote_literal</function> is labelled
1128+
<literal>STRICT</literal>, it will always return null when called with a
1129+
null argument. In the above example, if <literal>newvalue</> or
1130+
<literal>keyvalue</> were null, the entire dynamic query string would
1131+
become null, leading to an error from <command>EXECUTE</command>.
1132+
You can avoid this problem by using the <function>quote_nullable</>
1133+
function, which works the same as <function>quote_literal</> except that
1134+
when called with a null argument it returns the string <literal>NULL</>.
1135+
For example,
1136+
<programlisting>
1137+
EXECUTE 'UPDATE tbl SET '
1138+
|| quote_ident(colname)
1139+
|| ' = '
1140+
|| quote_nullable(newvalue)
1141+
|| ' WHERE key = '
1142+
|| quote_nullable(keyvalue);
1143+
</programlisting>
1144+
If you are dealing with values that might be null, you should usually
1145+
use <function>quote_nullable</> in place of <function>quote_literal</>.
1146+
</para>
1147+
1148+
<para>
1149+
As always, care must be taken to ensure that null values in a query do
1150+
not deliver unintended results. For example the <literal>WHERE</> clause
1151+
<programlisting>
1152+
'WHERE key = ' || quote_nullable(keyvalue)
1153+
</programlisting>
1154+
will never succeed if <literal>keyvalue</> is null, because the
1155+
result of using the equality operator <literal>=</> with a null operand
1156+
is always null. If you wish null to work like an ordinary key value,
1157+
you would need to rewrite the above as
1158+
<programlisting>
1159+
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
1160+
</programlisting>
1161+
(At present, <literal>IS NOT DISTINCT FROM</> is handled much less
1162+
efficiently than <literal>=</>, so don't do this unless you must.
1163+
See <xref linkend="functions-comparison"> for
1164+
more information on nulls and <literal>IS DISTINCT</>.)
11151165
</para>
11161166

11171167
<para>
11181168
Note that dollar quoting is only useful for quoting fixed text.
1119-
It would be a very bad idea to try todo the above example as:
1169+
It would be a very bad idea to try towrite this example as:
11201170
<programlisting>
11211171
EXECUTE 'UPDATE tbl SET '
11221172
|| quote_ident(colname)
@@ -1129,8 +1179,10 @@ EXECUTE 'UPDATE tbl SET '
11291179
happened to contain <literal>$$</>. The same objection would
11301180
apply to any other dollar-quoting delimiter you might pick.
11311181
So, to safely quote text that is not known in advance, you
1132-
<emphasis>must</> use <function>quote_literal</function>.
1182+
<emphasis>must</> use <function>quote_literal</>,
1183+
<function>quote_nullable</>, or <function>quote_ident</>, as appropriate.
11331184
</para>
1185+
</example>
11341186

11351187
<para>
11361188
A much larger example of a dynamic command and

‎src/backend/utils/adt/quote.c‎

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
*
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/utils/adt/quote.c,v 1.23 2008/01/01 19:45:52 momjian Exp $
10+
* $PostgreSQL: pgsql/src/backend/utils/adt/quote.c,v 1.24 2008/03/23 00:24:19 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -96,3 +96,19 @@ quote_literal(PG_FUNCTION_ARGS)
9696

9797
PG_RETURN_TEXT_P(result);
9898
}
99+
100+
/*
101+
* quote_nullable -
102+
* Returns a properly quoted literal, with null values returned
103+
* as the text string 'NULL'.
104+
*/
105+
Datum
106+
quote_nullable(PG_FUNCTION_ARGS)
107+
{
108+
if (PG_ARGISNULL(0))
109+
PG_RETURN_DATUM(DirectFunctionCall1(textin,
110+
CStringGetDatum("NULL")));
111+
else
112+
PG_RETURN_DATUM(DirectFunctionCall1(quote_literal,
113+
PG_GETARG_DATUM(0)));
114+
}

‎src/include/catalog/catversion.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.443 2008/03/22 01:55:14 ishii Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.444 2008/03/23 00:24:19 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200803221
56+
#defineCATALOG_VERSION_NO200803222
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.483 2008/03/22 01:55:14 ishii Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.484 2008/03/23 00:24:19 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2635,6 +2635,10 @@ DATA(insert OID = 1283 ( quote_literal PGNSP PGUID 12 1 0 f f t f i 1 25 "25
26352635
DESCR("quote a literal for usage in a querystring");
26362636
DATA(insertOID=1285 (quote_literalPGNSPPGUID1410fftfv125"2283"_null__null__null_"select pg_catalog.quote_literal($1::pg_catalog.text)"-_null__null_ ));
26372637
DESCR("quote a data value for usage in a querystring");
2638+
DATA(insertOID=1289 (quote_nullablePGNSPPGUID1210ffffi125"25"_null__null__null_quote_nullable-_null__null_ ));
2639+
DESCR("quote a possibly-null literal for usage in a querystring");
2640+
DATA(insertOID=1290 (quote_nullablePGNSPPGUID1410ffffv125"2283"_null__null__null_"select pg_catalog.quote_nullable($1::pg_catalog.text)"-_null__null_ ));
2641+
DESCR("quote a possibly-null data value for usage in a querystring");
26382642

26392643
DATA(insertOID=1798 (oidinPGNSPPGUID1210fftfi126"2275"_null__null__null_oidin-_null__null_ ));
26402644
DESCR("I/O");

‎src/include/utils/builtins.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.308 2008/01/01 19:45:59 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.309 2008/03/23 00:24:20 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -914,6 +914,7 @@ extern int32 type_maximum_size(Oid type_oid, int32 typemod);
914914
/* quote.c */
915915
externDatumquote_ident(PG_FUNCTION_ARGS);
916916
externDatumquote_literal(PG_FUNCTION_ARGS);
917+
externDatumquote_nullable(PG_FUNCTION_ARGS);
917918

918919
/* guc.c */
919920
externDatumshow_config_by_name(PG_FUNCTION_ARGS);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp