From 7de81124d52422a513725af7f40446613e6bdda8 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 | 36 +++++++++++- doc/src/sgml/plpgsql.sgml | 94 +++++++++++++++++++++++++------- src/backend/utils/adt/quote.c | 18 +++++- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 6 +- src/include/utils/builtins.h | 3 +- 6 files changed, 134 insertions(+), 27 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index ec138a5c2dc..499faa0c92d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -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 29357e4ca9b..73873614f64 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -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 ad9335d7b3c..519c6d874b5 100644 --- a/src/backend/utils/adt/quote.c +++ b/src/backend/utils/adt/quote.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/quote.c,v 1.23 2008/01/01 19:45:52 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/quote.c,v 1.24 2008/03/23 00:24:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -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 7bbdbe658a1..009cf8abf66 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.443 2008/03/22 01:55:14 ishii Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.444 2008/03/23 00:24:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -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 3aea12b838f..b62ff0d7523 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.483 2008/03/22 01:55:14 ishii Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.484 2008/03/23 00:24:19 tgl Exp $ * * NOTES * 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 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 7b7a54a6e22..5dbc00fce53 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.308 2008/01/01 19:45:59 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.309 2008/03/23 00:24:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -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