Hi patchers,
Per discussion on -hackers, I've implemented a new internal function
quote_nullable, as an alternative to quote_literal. The difference is
that quote_nullable returns the text value 'NULL' on NULL input, which
is suitable for insertion into an SQL statement.
The idea is that when you're writing a plpgsql function with dynamic
queries, you can use quote_nullable for values which are
possibly-null. You're still responsible for handling NULLs sensibly
within your query, but at least you get a syntactically valid SQL
statement.
I've included doc updates but no new regression tests. I did not add
tests because there are currently no tests for quote_literal and when
I recently suggested addition of tests for quote_ident [1] they were
rejected. I still don't fully understand the criteria for inclusion
of regression tests, but this is a similar situation, so I'm following
the same guidance.
Patch compiles cleanly and passes make check on x86 gentoo.
Thanks for your time,
BJ
[1] http://archives.postgresql.org/pgsql-patches/2007-10/msg00080.php
On 10/11/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Well, it's clearly useful in INSERT and UPDATE. For WHERE cases, you
> might or might not be able to use it, but I note that quote_nullable()
> would work much more like what happens if you use a parameter symbol
> and then bind NULL as the actual parameter value ...
>
> In hindsight we should probably have done quote_literal the way the OP
> suggests, but I concur that it's too late to change it. An additional
> function seems a reasonable compromise.
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /home/brendanjurd/src/pgsql.cvs/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.400
diff -c -r1.400 func.sgml
*** doc/src/sgml/func.sgml 11 Oct 2007 02:43:55 -0000 1.400
--- doc/src/sgml/func.sgml 11 Oct 2007 15:53:37 -0000
***************
*** 1263,1268 ****
--- 1263,1271 ----
<primary>quote_literal</primary>
</indexterm>
<indexterm>
+ <primary>quote_nullable</primary>
+ </indexterm>
+ <indexterm>
<primary>repeat</primary>
</indexterm>
<indexterm>
***************
*** 1532,1545 ****
<entry><type>text</type></entry>
<entry>
Return the given string suitably quoted to be used as a string literal
! in an <acronym>SQL</acronym> statement string.
! Embedded single-quotes and backslashes are properly doubled.
</entry>
<entry><literal>quote_literal('O\'Reilly')</literal></entry>
<entry><literal>'O''Reilly'</literal></entry>
</row>
<row>
<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>
<entry><type>setof text[]</type></entry>
<entry>
--- 1535,1564 ----
<entry><type>text</type></entry>
<entry>
Return the given string suitably quoted to be used as a string literal
! in an <acronym>SQL</acronym> statement string. Embedded single-quotes
! and backslashes are properly doubled. Note that
! <literal><function>quote_literal</function></literal> returns null on
! null input; use <literal><function>quote_nullable</function></literal>
! instead if you want to use nulls in your <acronym>SQL</acronym>
! statement.
</entry>
<entry><literal>quote_literal('O\'Reilly')</literal></entry>
<entry><literal>'O''Reilly'</literal></entry>
</row>
<row>
+
<entry><literal><function>quote_nullable</function>(<parameter>string</parameter>)</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>
+ Return the given string (which may be null) suitably quoted to be used
+ as a string literal in an <acronym>SQL</acronym> statement string.
+ Embedded single-quotes and backslashes are properly doubled.
+ </entry>
+ <entry><literal>quote_literal(NULL)</literal></entry>
+ <entry><literal>NULL</literal></entry>
+ </row>
+
+ <row>
<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>
<entry><type>setof text[]</type></entry>
<entry>
Index: src/backend/utils/adt/quote.c
===================================================================
RCS file: /home/brendanjurd/src/pgsql.cvs/pgsql/src/backend/utils/adt/quote.c,v
retrieving revision 1.22
diff -c -r1.22 quote.c
*** src/backend/utils/adt/quote.c 27 Feb 2007 23:48:08 -0000 1.22
--- src/backend/utils/adt/quote.c 11 Oct 2007 15:52:45 -0000
***************
*** 96,98 ****
--- 96,114 ----
PG_RETURN_TEXT_P(result);
}
+
+ /*
+ * quote_nullable -
+ * Returns a properly quoted literal, with null values returned as the SQL
+ * value 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)));
+ }
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /home/brendanjurd/src/pgsql.cvs/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.473
diff -c -r1.473 pg_proc.h
*** src/include/catalog/pg_proc.h 25 Sep 2007 20:03:38 -0000 1.473
--- src/include/catalog/pg_proc.h 11 Oct 2007 14:08:04 -0000
***************
*** 2627,2636 ****
DATA(insert OID = 1768 ( to_char PGNSP PGUID 12 1 0 f f
t f s 2 25 "1186 25" _null_ _null_ _null_ interval_to_char - _null_ _null_ ));
DESCR("format interval to text");
! DATA(insert OID = 1282 ( quote_ident PGNSP PGUID 12 1 0 f f t f i 1 25
"25" _null_ _null_ _null_ quote_ident - _null_ _null_ ));
DESCR("quote an identifier for usage in a querystring");
! DATA(insert OID = 1283 ( quote_literal PGNSP PGUID 12 1 0 f f t f i 1 25
"25" _null_ _null_ _null_ quote_literal - _null_ _null_ ));
DESCR("quote a literal 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");
--- 2627,2638 ----
DATA(insert OID = 1768 ( to_char PGNSP PGUID 12 1 0 f f
t f s 2 25 "1186 25" _null_ _null_ _null_ interval_to_char - _null_ _null_ ));
DESCR("format interval to text");
! DATA(insert OID = 1282 ( quote_ident PGNSP PGUID 12 1 0 f f t f i 1 25
"25" _null_ _null_ _null_ quote_ident - _null_ _null_ ));
DESCR("quote an identifier for usage in a querystring");
! DATA(insert OID = 1283 ( quote_literal PGNSP PGUID 12 1 0 f f t f i 1 25
"25" _null_ _null_ _null_ quote_literal - _null_ _null_ ));
DESCR("quote a literal for usage in a querystring");
+ DATA(insert OID = 1285 ( 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 = 1798 ( oidin PGNSP PGUID 12 1 0 f
f t f i 1 26 "2275" _null_ _null_ _null_ oidin - _null_ _null_ ));
DESCR("I/O");
Index: src/include/utils/builtins.h
===================================================================
RCS file: /home/brendanjurd/src/pgsql.cvs/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.304
diff -c -r1.304 builtins.h
*** src/include/utils/builtins.h 24 Sep 2007 01:29:30 -0000 1.304
--- src/include/utils/builtins.h 11 Oct 2007 14:08:04 -0000
***************
*** 916,921 ****
--- 916,922 ----
/* 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);
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
