On 06/02/11 10:54, Jan Urbański wrote:
> On 04/02/11 18:10, Hitoshi Harada wrote:
>> 2011/1/11 Jan Urbański <[email protected]>:
>>> Here's a patch that adds a few PL/Python functions for quoting strings.
>>> It's an incremental patch on top of the plpython-refactor patch sent in
>>> http://archives.postgresql.org/message-id/[email protected].
>>>
>>> Git branch for this patch:
>>> https://github.com/wulczer/postgres/tree/functions
>>>
>>> The new functions are plpy.quote_literal, plpy.quote_nullable and
>>> plpy.quote_ident, and work just like their sql or plperl equivalents.
>>>
>>
>> I reviewed this.
>>
>> The patch applies and compiles cleanly and all the tests are passed.
>> The patch adds 3 functions which works as the corresponding SQL
>> functions. The test is enough, without any additional docs. No
>> feature/performance issues found.
>>
>> I mark this "Reader for Committer".
>
> Thanks!
>
> I guess a short paragraph in the Utility Functions section of the
> PL/Python docs would be in order, I'll try to add it today.
Added docs and merged with master.
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index e05c293..8a995b2 100644
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*************** $$ LANGUAGE plpythonu;
*** 997,1002 ****
--- 997,1018 ----
<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 builtin 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/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 16d78ae..292e360 100644
*** a/src/pl/plpython/Makefile
--- b/src/pl/plpython/Makefile
*************** REGRESS = \
*** 79,84 ****
--- 79,85 ----
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
index ...b33ee3f .
*** a/src/pl/plpython/expected/plpython_quote.out
--- b/src/pl/plpython/expected/plpython_quote.out
***************
*** 0 ****
--- 1,87 ----
+ -- 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)
+
+ -- test errors
+ SELECT quote(NULL::text, 'literal');
+ ERROR: TypeError: argument 1 must be string, not None
+ CONTEXT: PL/Python function "quote"
+ SELECT quote(NULL::text, 'ident');
+ ERROR: TypeError: argument 1 must be string, not None
+ CONTEXT: PL/Python function "quote"
+ SELECT quote('abc', 'random');
+ ERROR: plpy.Error: unrecognized quote type random
+ CONTEXT: PL/Python function "quote"
+ DO $$ plpy.quote_literal(3) $$ LANGUAGE plpythonu;
+ ERROR: TypeError: argument 1 must be string, not int
+ CONTEXT: PL/Python anonymous code block
+ DO $$ plpy.quote_literal(None) $$ LANGUAGE plpythonu;
+ ERROR: TypeError: argument 1 must be string, not None
+ CONTEXT: PL/Python anonymous code block
+ DO $$ plpy.quote_literal({'a': 'b'}) $$ LANGUAGE plpythonu;
+ ERROR: TypeError: argument 1 must be string, not dict
+ CONTEXT: PL/Python anonymous code block
+ DO $$ plpy.quote_literal() $$ LANGUAGE plpythonu;
+ ERROR: TypeError: function takes exactly 1 argument (0 given)
+ CONTEXT: PL/Python anonymous code block
+ DO $$ plpy.quote_literal(1, 2) $$ LANGUAGE plpythonu;
+ ERROR: TypeError: function takes exactly 1 argument (2 given)
+ CONTEXT: PL/Python anonymous code block
+ DO $$ plpy.quote_nullable([1, 2]) $$ LANGUAGE plpythonu;
+ ERROR: TypeError: argument 1 must be string or None, not list
+ CONTEXT: PL/Python anonymous code block
+ DO $$ plpy.quote_ident(1.5) $$ LANGUAGE plpythonu;
+ ERROR: TypeError: argument 1 must be string, not float
+ CONTEXT: PL/Python anonymous code block
diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out
index d92c987..961f6c0 100644
*** a/src/pl/plpython/expected/plpython_test.out
--- b/src/pl/plpython/expected/plpython_test.out
*************** contents.sort()
*** 43,51 ****
return ", ".join(contents)
$$ LANGUAGE plpythonu;
select module_contents();
! module_contents
! -------------------------------------------------------------------------------------------
! Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, warning
(1 row)
CREATE FUNCTION elog_test() RETURNS void
--- 43,51 ----
return ", ".join(contents)
$$ LANGUAGE plpythonu;
select module_contents();
! 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
index fff7de7..39c7e53 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** static PyObject *PLy_spi_execute_query(c
*** 2558,2563 ****
--- 2558,2567 ----
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 *, PyObject *);
+ static PyObject *PLy_quote_nullable(PyObject *, PyObject *);
+ static PyObject *PLy_quote_ident(PyObject *, PyObject *);
+
static PyMethodDef PLy_plan_methods[] = {
{"status", PLy_plan_status, METH_VARARGS, NULL},
*************** static PyMethodDef PLy_methods[] = {
*** 2672,2677 ****
--- 2676,2688 ----
*/
{"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}
};
*************** PLy_output(volatile int level, PyObject
*** 3608,3613 ****
--- 3619,3683 ----
return Py_None;
}
+ static PyObject *
+ PLy_quote_literal(PyObject *self, PyObject *args)
+ {
+ 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)
+ {
+ 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)
+ {
+ char *str;
+ char *quoted;
+ PyObject *ret;
+
+ if (!PyArg_ParseTuple(args, "s", &str))
+ return NULL;
+
+ /* cast to char * to avoid a "discards qualifier" warning */
+ quoted = (char *) quote_identifier(str);
+ ret = PyString_FromString(quoted);
+ /*
+ * quote_identifier sometimes returns a palloc'd string, and sometimes
+ * just the pointer passed
+ */
+ if (quoted != str)
+ pfree(quoted);
+
+ return ret;
+ }
/*
* Get the name of the last procedure called by the backend (the
diff --git a/src/pl/plpython/sql/plpython_quote.sql b/src/pl/plpython/sql/plpython_quote.sql
index ...15cab3c .
*** a/src/pl/plpython/sql/plpython_quote.sql
--- b/src/pl/plpython/sql/plpython_quote.sql
***************
*** 0 ****
--- 1,45 ----
+ -- 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);
+
+ -- test errors
+ SELECT quote(NULL::text, 'literal');
+ SELECT quote(NULL::text, 'ident');
+ SELECT quote('abc', 'random');
+ DO $$ plpy.quote_literal(3) $$ LANGUAGE plpythonu;
+ DO $$ plpy.quote_literal(None) $$ LANGUAGE plpythonu;
+ DO $$ plpy.quote_literal({'a': 'b'}) $$ LANGUAGE plpythonu;
+ DO $$ plpy.quote_literal() $$ LANGUAGE plpythonu;
+ DO $$ plpy.quote_literal(1, 2) $$ LANGUAGE plpythonu;
+ DO $$ plpy.quote_nullable([1, 2]) $$ LANGUAGE plpythonu;
+ DO $$ plpy.quote_ident(1.5) $$ LANGUAGE plpythonu;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
