From 5a8cf225019ba83c6e6384f008576b5cf20d4208 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 23 Mar 2008 00:24:20 +0000 Subject: [PATCH] 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 called with a null argument. This is often a much more useful behavior for constructing dynamic queries. Add more discussion to the documentation about how to use these functions. Brendan Jurd --- doc/src/sgml/func.sgml | 34 ++++++++++++ doc/src/sgml/plpgsql.sgml | 92 +++++++++++++++++++++++++------- src/backend/utils/adt/quote.c | 16 ++++++ src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.h | 4 ++ src/include/utils/builtins.h | 1 + 6 files changed, 128 insertions(+), 21 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 5e17d3471a..f0f3ee84cd 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1262,6 +1262,9 @@ quote_literal + + quote_nullable + repeat @@ -1523,6 +1526,7 @@ 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 . quote_ident('Foo bar') "Foo bar" @@ -1535,6 +1539,10 @@ 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 . quote_literal('O\'Reilly') 'O''Reilly' @@ -1551,6 +1559,32 @@ '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 . + + 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' + + regexp_matches(string text, pattern text [, flags text]) setof text[] diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 7fe8723a4e..96e5034149 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1066,6 +1066,24 @@ EXECUTE command-string INT + + Quoting values in dynamic queries + + + quote_ident + use in PL/PgSQL + + + + quote_literal + use in PL/PgSQL + + + + quote_nullable + use in PL/PgSQL + + When working with dynamic commands you will often have to handle escaping of single quotes. The recommended method for quoting fixed text in your @@ -1091,32 +1109,64 @@ EXECUTE 'UPDATE tbl SET ' - - quote_ident - use in PL/PgSQL - - - - quote_literal - use in PL/PgSQL - - This example demonstrates the use of the quote_ident and - quote_literal functions. For safety, - expressions containing column and table identifiers should be - passed to quote_ident. Expressions containing - values that should be literal strings in the constructed command - should be passed to quote_literal. Both - take the appropriate steps to return the input text enclosed in - double or single quotes respectively, with any embedded special - characters properly escaped. + quote_literal functions (see ). For safety, expressions containing column + or table identifiers should be passed through + quote_ident before insertion in a dynamic query. + Expressions containing values that should be literal strings in the + constructed command should be passed through quote_literal. + These functions take the appropriate steps to return the input text + enclosed in double or single quotes respectively, with any embedded + special characters properly escaped. + + + + Because quote_literal is labelled + STRICT, it will always return null when called with a + null argument. In the above example, if newvalue or + keyvalue were null, the entire dynamic query string would + become null, leading to an error from EXECUTE. + You can avoid this problem by using the quote_nullable + function, which works the same as quote_literal except that + when called with a null argument it returns the string NULL. + For example, + +EXECUTE 'UPDATE tbl SET ' + || quote_ident(colname) + || ' = ' + || quote_nullable(newvalue) + || ' WHERE key = ' + || quote_nullable(keyvalue); + + If you are dealing with values that might be null, you should usually + use quote_nullable in place of quote_literal. + + + + As always, care must be taken to ensure that null values in a query do + not deliver unintended results. For example the WHERE clause + + 'WHERE key = ' || quote_nullable(keyvalue) + + will never succeed if keyvalue is null, because the + result of using the equality operator = with a null operand + is always null. If you wish null to work like an ordinary key value, + you would need to rewrite the above as + + 'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue) + + (At present, IS NOT DISTINCT FROM is handled much less + efficiently than =, so don't do this unless you must. + See for + more information on nulls and IS DISTINCT.) Note that dollar quoting is only useful for quoting fixed text. - It would be a very bad idea to try to do the above example as: + It would be a very bad idea to try to write this example as: EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) @@ -1129,8 +1179,10 @@ EXECUTE 'UPDATE tbl SET ' happened to contain $$. The same objection would apply to any other dollar-quoting delimiter you might pick. So, to safely quote text that is not known in advance, you - must use quote_literal. + must use quote_literal, + quote_nullable, or quote_ident, as appropriate. + A much larger example of a dynamic command and diff --git a/src/backend/utils/adt/quote.c b/src/backend/utils/adt/quote.c index 85a727d85e..be77a0a026 100644 --- a/src/backend/utils/adt/quote.c +++ b/src/backend/utils/adt/quote.c @@ -96,3 +96,19 @@ quote_literal(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(result); } + +/* + * 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_DATUM(DirectFunctionCall1(textin, + CStringGetDatum("NULL"))); + else + PG_RETURN_DATUM(DirectFunctionCall1(quote_literal, + PG_GETARG_DATUM(0))); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 052e9244de..b9f34f2a90 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200803221 +#define CATALOG_VERSION_NO 200803222 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 8063f7c6c6..61c035eefd 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2635,6 +2635,10 @@ DATA(insert OID = 1283 ( quote_literal PGNSP PGUID 12 1 0 f f t f i 1 25 "25 DESCR("quote a literal for usage in a querystring"); DATA(insert OID = 1285 ( quote_literal PGNSP PGUID 14 1 0 f f t f v 1 25 "2283" _null_ _null_ _null_ "select pg_catalog.quote_literal($1::pg_catalog.text)" - _null_ _null_ )); DESCR("quote a data value for usage in a querystring"); +DATA(insert OID = 1289 ( quote_nullable PGNSP PGUID 12 1 0 f f f f i 1 25 "25" _null_ _null_ _null_ quote_nullable - _null_ _null_ )); +DESCR("quote a possibly-null literal for usage in a querystring"); +DATA(insert OID = 1290 ( quote_nullable PGNSP PGUID 14 1 0 f f f f v 1 25 "2283" _null_ _null_ _null_ "select pg_catalog.quote_nullable($1::pg_catalog.text)" - _null_ _null_ )); +DESCR("quote a possibly-null data value for usage in a querystring"); DATA(insert OID = 1798 ( oidin PGNSP PGUID 12 1 0 f f t f i 1 26 "2275" _null_ _null_ _null_ oidin - _null_ _null_ )); DESCR("I/O"); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 7d3eaee121..cd7d99ae9f 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -914,6 +914,7 @@ extern int32 type_maximum_size(Oid type_oid, int32 typemod); /* quote.c */ extern Datum quote_ident(PG_FUNCTION_ARGS); extern Datum quote_literal(PG_FUNCTION_ARGS); +extern Datum quote_nullable(PG_FUNCTION_ARGS); /* guc.c */ extern Datum show_config_by_name(PG_FUNCTION_ARGS); -- 2.39.5