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/4d135170.3080...@wulczer.org.
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.
Cheers,
Jan
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 2307627..ba57519 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** static PyObject *PLy_spi_execute_query(c
*** 2524,2529 ****
--- 2524,2533 ----
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[] = {
*** 2638,2643 ****
--- 2642,2654 ----
*/
{"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
*** 3508,3513 ****
--- 3519,3583 ----
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
