Add PL/Python functions for quoting strings

petere · petere · commit 1c51c7d5ffd4 · 2011-02-22T23:41:23.000+02:00
Add functions plpy.quote_ident, plpy.quote_literal,
plpy.quote_nullable, which wrap the equivalent SQL functions.

To be able to propagate char * constness properly, make the argument
of quote_literal_cstr() const char *.  This also makes it more
consistent with quote_identifier().

Jan Urbański, reviewed by Hitoshi Harada, some refinements by Peter
Eisentraut
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
@@ -997,6 +997,23 @@ $$ LANGUAGE plpythonu;
    <xref linkend="guc-client-min-messages"> configuration
    variables. See <xref linkend="runtime-config"> for more information.
   </para>
+
+  <para>
+   Another set of utility functions are
+   <literal>plpy.quote_literal(<replaceable>string</>)</literal>,
+   <literal>plpy.quote_nullable(<replaceable>string</>)</literal>, and
+   <literal>plpy.quote_ident(<replaceable>string</>)</literal>.  They
+   are equivalent to the built-in quoting functions described in <xref
+   linkend="functions-string">.  They are useful when constructing
+   ad-hoc queries.  A PL/Python equivalent of dynamic SQL from <xref
+   linkend="plpgsql-quote-literal-example"> would be:
+<programlisting>
+plpy.execute("UPDATE tbl SET %s = %s where key = %s" % (
+    plpy.quote_ident(colname),
+    plpy.quote_nullable(newvalue),
+    plpy.quote_literal(keyvalue)))
+</programlisting>
+  </para>
  </sect1>
 
  <sect1 id="plpython-envar">
diff --git a/src/backend/utils/adt/quote.c b/src/backend/utils/adt/quote.c
@@ -43,9 +43,9 @@ quote_ident(PG_FUNCTION_ARGS)
  * uses this for before thinking you know better.
  */
 static size_t
-quote_literal_internal(char *dst, char *src, size_t len)
+quote_literal_internal(char *dst, const char *src, size_t len)
 {
-	char	   *s;
+	const char *s;
 	char	   *savedst = dst;
 
 	for (s = src; s < src + len; s++)
@@ -99,7 +99,7 @@ quote_literal(PG_FUNCTION_ARGS)
  *	  returns a properly quoted literal
  */
 char *
-quote_literal_cstr(char *rawstr)
+quote_literal_cstr(const char *rawstr)
 {
 	char	   *result;
 	int			len;
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
@@ -988,7 +988,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 char *quote_literal_cstr(char *rawstr);
+extern char *quote_literal_cstr(const char *rawstr);
 extern Datum quote_nullable(PG_FUNCTION_ARGS);
 
 /* guc.c */
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
@@ -79,6 +79,7 @@ REGRESS = \
 	plpython_types \
 	plpython_error \
 	plpython_unicode \
+	plpython_quote \
 	plpython_drop
 # where to find psql for running the tests
 PSQLDIR = $(bindir)
diff --git a/src/pl/plpython/expected/plpython_quote.out b/src/pl/plpython/expected/plpython_quote.out
@@ -0,0 +1,56 @@
+-- test quoting functions
+CREATE FUNCTION quote(t text, how text) RETURNS text AS $$
+    if how == "literal":
+        return plpy.quote_literal(t)
+    elif how == "nullable":
+        return plpy.quote_nullable(t)
+    elif how == "ident":
+        return plpy.quote_ident(t)
+    else:
+        raise plpy.Error("unrecognized quote type %s" % how)
+$$ LANGUAGE plpythonu;
+SELECT quote(t, 'literal') FROM (VALUES
+       ('abc'),
+       ('a''bc'),
+       ('''abc'''),
+       (''),
+       (''''),
+       ('xyzv')) AS v(t);
+   quote   
+-----------
+ 'abc'
+ 'a''bc'
+ '''abc'''
+ ''
+ ''''
+ 'xyzv'
+(6 rows)
+
+SELECT quote(t, 'nullable') FROM (VALUES
+       ('abc'),
+       ('a''bc'),
+       ('''abc'''),
+       (''),
+       (''''),
+       (NULL)) AS v(t);
+   quote   
+-----------
+ 'abc'
+ 'a''bc'
+ '''abc'''
+ ''
+ ''''
+ NULL
+(6 rows)
+
+SELECT quote(t, 'ident') FROM (VALUES
+       ('abc'),
+       ('a b c'),
+       ('a " ''abc''')) AS v(t);
+    quote     
+--------------
+ abc
+ "a b c"
+ "a "" 'abc'"
+(3 rows)
+
diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out
@@ -43,9 +43,9 @@ contents.sort()
 return ", ".join(contents)
 $$ LANGUAGE plpythonu;
 select module_contents();
-                                      module_contents                                      
--------------------------------------------------------------------------------------------
- Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, warning
+                                                            module_contents                                                            
+---------------------------------------------------------------------------------------------------------------------------------------
+ Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable, warning
 (1 row)
 
 CREATE FUNCTION elog_test() RETURNS void
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
@@ -2637,6 +2637,10 @@ static PyObject *PLy_spi_execute_query(char *query, long limit);
 static PyObject *PLy_spi_execute_plan(PyObject *, PyObject *, long);
 static PyObject *PLy_spi_execute_fetch_result(SPITupleTable *, int, int);
 
+static PyObject *PLy_quote_literal(PyObject *self, PyObject *args);
+static PyObject *PLy_quote_nullable(PyObject *self, PyObject *args);
+static PyObject *PLy_quote_ident(PyObject *self, PyObject *args);
+
 
 static PyMethodDef PLy_plan_methods[] = {
 	{"status", PLy_plan_status, METH_VARARGS, NULL},
@@ -2751,6 +2755,13 @@ static PyMethodDef PLy_methods[] = {
 	 */
 	{"execute", PLy_spi_execute, METH_VARARGS, NULL},
 
+	/*
+	 * escaping strings
+	 */
+	{"quote_literal", PLy_quote_literal, METH_VARARGS, NULL},
+	{"quote_nullable", PLy_quote_nullable, METH_VARARGS, NULL},
+	{"quote_ident", PLy_quote_ident, METH_VARARGS, NULL},
+
 	{NULL, NULL, 0, NULL}
 };
 
@@ -3688,6 +3699,60 @@ PLy_output(volatile int level, PyObject *self, PyObject *args)
 }
 
 
+static PyObject *
+PLy_quote_literal(PyObject *self, PyObject *args)
+{
+	const char *str;
+	char	   *quoted;
+	PyObject   *ret;
+
+	if (!PyArg_ParseTuple(args, "s", &str))
+		return NULL;
+
+	quoted = quote_literal_cstr(str);
+	ret = PyString_FromString(quoted);
+	pfree(quoted);
+
+	return ret;
+}
+
+static PyObject *
+PLy_quote_nullable(PyObject *self, PyObject *args)
+{
+	const char *str;
+	char	   *quoted;
+	PyObject   *ret;
+
+	if (!PyArg_ParseTuple(args, "z", &str))
+		return NULL;
+
+	if (str == NULL)
+		return PyString_FromString("NULL");
+
+	quoted = quote_literal_cstr(str);
+	ret = PyString_FromString(quoted);
+	pfree(quoted);
+
+	return ret;
+}
+
+static PyObject *
+PLy_quote_ident(PyObject *self, PyObject *args)
+{
+	const char *str;
+	const char *quoted;
+	PyObject   *ret;
+
+	if (!PyArg_ParseTuple(args, "s", &str))
+		return NULL;
+
+	quoted = quote_identifier(str);
+	ret = PyString_FromString(quoted);
+
+	return ret;
+}
+
+
 /*
  * Get the name of the last procedure called by the backend (the
  * innermost, if a plpython procedure call calls the backend and the
diff --git a/src/pl/plpython/sql/plpython_quote.sql b/src/pl/plpython/sql/plpython_quote.sql
@@ -0,0 +1,33 @@
+-- test quoting functions
+
+CREATE FUNCTION quote(t text, how text) RETURNS text AS $$
+    if how == "literal":
+        return plpy.quote_literal(t)
+    elif how == "nullable":
+        return plpy.quote_nullable(t)
+    elif how == "ident":
+        return plpy.quote_ident(t)
+    else:
+        raise plpy.Error("unrecognized quote type %s" % how)
+$$ LANGUAGE plpythonu;
+
+SELECT quote(t, 'literal') FROM (VALUES
+       ('abc'),
+       ('a''bc'),
+       ('''abc'''),
+       (''),
+       (''''),
+       ('xyzv')) AS v(t);
+
+SELECT quote(t, 'nullable') FROM (VALUES
+       ('abc'),
+       ('a''bc'),
+       ('''abc'''),
+       (''),
+       (''''),
+       (NULL)) AS v(t);
+
+SELECT quote(t, 'ident') FROM (VALUES
+       ('abc'),
+       ('a b c'),
+       ('a " ''abc''')) AS v(t);
