On 06/26/2013 04:47 PM, Szymon Guz wrote:
Attached patch has all changes against trunk code.
There is added a function for conversion from Postgres numeric to
Python Decimal. The Decimal type is taken from cdecimal.Decimal, if it
is available. It is an external library, quite fast, but may be not
available. If it is not available, then decimal.Decimal will be used.
It is in standard Python library, however it is rather slow.
The initialization is done in the conversion function, the pointer to
a proper Decimal constructor is stored as static variable inside the
function and is lazy initialized.
The documentation is updated.
I've tested this version with python 2.7 with and without cdecimal and
also with 3.3 that has the faster decimal performance. It seems fine.
The v5 version of the patch makes only white-space changes to
plpy_main.c you should excluded that from the patch if your making a new
version (I have done this in the v6 version I'm attaching)
Tests for python 2 and 3 have been added. They work only with standard
decimal.Decimal, as the type is printed in the *.out files. I think
there is nothing we can do with that now.
I think we should make test_type_conversion_numeric to do something
that generates the same output in both cases. ie
py.info(str(x)). I downside of having the test fail on installs with
cdecimal installed is much greater than any benefit we get by ensuring
that the type is really decimal.
I've attached a v6 version of the patch that does this, do you agree
with my thinking?
Steve
regards,
Szymon
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
new file mode 100644
index aaf758d..da27874
*** a/doc/src/sgml/plpython.sgml
--- b/doc/src/sgml/plpython.sgml
*************** $$ LANGUAGE plpythonu;
*** 308,321 ****
</para>
</listitem>
<listitem>
<para>
! PostgreSQL <type>real</type>, <type>double</type>,
! and <type>numeric</type> are converted to
! Python <type>float</type>. Note that for
! the <type>numeric</type> this loses information and can lead to
! incorrect results. This might be fixed in a future
! release.
</para>
</listitem>
--- 308,326 ----
</para>
</listitem>
+ <listitem>
+ <para>
+ PostgreSQL <type>real</type> and <type>double</type> are converted to
+ Python <type>float</type>.
+ </para>
+ </listitem>
+
<listitem>
<para>
! PostgreSQL <type>numeric</type> is converted to
! Python <type>Decimal</type>. This type is imported from
! <literal>cdecimal</literal> package if it is available. If cdecimal
! cannot be used, then <literal>decimal.Decimal</literal> will be used.
</para>
</listitem>
diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
new file mode 100644
index 4641345..46308ed
*** a/src/pl/plpython/expected/plpython_types.out
--- b/src/pl/plpython/expected/plpython_types.out
*************** CONTEXT: PL/Python function "test_type_
*** 213,248 ****
(1 row)
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
! plpy.info(x, type(x))
return x
$$ LANGUAGE plpythonu;
! /* The current implementation converts numeric to float. */
SELECT * FROM test_type_conversion_numeric(100);
! INFO: (100.0, <type 'float'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! 100.0
(1 row)
SELECT * FROM test_type_conversion_numeric(-100);
! INFO: (-100.0, <type 'float'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! -100.0
(1 row)
SELECT * FROM test_type_conversion_numeric(5000000000.5);
! INFO: (5000000000.5, <type 'float'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
5000000000.5
(1 row)
SELECT * FROM test_type_conversion_numeric(null);
! INFO: (None, <type 'NoneType'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
--- 213,264 ----
(1 row)
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
! plpy.info(str(x))
return x
$$ LANGUAGE plpythonu;
! /* The current implementation converts numeric to decimal.Decimal. */
SELECT * FROM test_type_conversion_numeric(100);
! INFO: 100
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! 100
(1 row)
SELECT * FROM test_type_conversion_numeric(-100);
! INFO: -100
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! -100
(1 row)
SELECT * FROM test_type_conversion_numeric(5000000000.5);
! INFO: 5000000000.5
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
5000000000.5
(1 row)
+ SELECT * FROM test_type_conversion_numeric(1234567890.0987654321);
+ INFO: 1234567890.0987654321
+ CONTEXT: PL/Python function "test_type_conversion_numeric"
+ test_type_conversion_numeric
+ ------------------------------
+ 1234567890.0987654321
+ (1 row)
+
+ SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321);
+ INFO: -1234567890.0987654321
+ CONTEXT: PL/Python function "test_type_conversion_numeric"
+ test_type_conversion_numeric
+ ------------------------------
+ -1234567890.0987654321
+ (1 row)
+
SELECT * FROM test_type_conversion_numeric(null);
! INFO: None
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
diff --git a/src/pl/plpython/expected/plpython_types_3.out b/src/pl/plpython/expected/plpython_types_3.out
new file mode 100644
index 511ef5a..d791fb6
*** a/src/pl/plpython/expected/plpython_types_3.out
--- b/src/pl/plpython/expected/plpython_types_3.out
*************** CONTEXT: PL/Python function "test_type_
*** 213,248 ****
(1 row)
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
! plpy.info(x, type(x))
return x
$$ LANGUAGE plpython3u;
! /* The current implementation converts numeric to float. */
SELECT * FROM test_type_conversion_numeric(100);
! INFO: (100.0, <class 'float'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! 100.0
(1 row)
SELECT * FROM test_type_conversion_numeric(-100);
! INFO: (-100.0, <class 'float'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! -100.0
(1 row)
SELECT * FROM test_type_conversion_numeric(5000000000.5);
! INFO: (5000000000.5, <class 'float'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
5000000000.5
(1 row)
SELECT * FROM test_type_conversion_numeric(null);
! INFO: (None, <class 'NoneType'>)
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
--- 213,264 ----
(1 row)
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
! plpy.info(str(x))
return x
$$ LANGUAGE plpython3u;
! /* The current implementation converts numeric to decimal.Decimal. */
SELECT * FROM test_type_conversion_numeric(100);
! INFO: 100
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! 100
(1 row)
SELECT * FROM test_type_conversion_numeric(-100);
! INFO: -100
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
! -100
(1 row)
SELECT * FROM test_type_conversion_numeric(5000000000.5);
! INFO: 5000000000.5
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
5000000000.5
(1 row)
+ SELECT * FROM test_type_conversion_numeric(1234567890.0987654321);
+ INFO: 1234567890.0987654321
+ CONTEXT: PL/Python function "test_type_conversion_numeric"
+ test_type_conversion_numeric
+ ------------------------------
+ 1234567890.0987654321
+ (1 row)
+
+ SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321);
+ INFO: -1234567890.0987654321
+ CONTEXT: PL/Python function "test_type_conversion_numeric"
+ test_type_conversion_numeric
+ ------------------------------
+ -1234567890.0987654321
+ (1 row)
+
SELECT * FROM test_type_conversion_numeric(null);
! INFO: None
CONTEXT: PL/Python function "test_type_conversion_numeric"
test_type_conversion_numeric
------------------------------
diff --git a/src/pl/plpython/plpy_typeio.c b/src/pl/plpython/plpy_typeio.c
new file mode 100644
index 6a9a2cb..e04c2f9
*** a/src/pl/plpython/plpy_typeio.c
--- b/src/pl/plpython/plpy_typeio.c
***************
*** 18,23 ****
--- 18,24 ----
#include "utils/memutils.h"
#include "utils/syscache.h"
#include "utils/typcache.h"
+ #include "utils/numeric.h"
#include "plpython.h"
*************** static void PLy_output_datum_func2(PLyOb
*** 35,41 ****
static PyObject *PLyBool_FromBool(PLyDatumToOb *arg, Datum d);
static PyObject *PLyFloat_FromFloat4(PLyDatumToOb *arg, Datum d);
static PyObject *PLyFloat_FromFloat8(PLyDatumToOb *arg, Datum d);
! static PyObject *PLyFloat_FromNumeric(PLyDatumToOb *arg, Datum d);
static PyObject *PLyInt_FromInt16(PLyDatumToOb *arg, Datum d);
static PyObject *PLyInt_FromInt32(PLyDatumToOb *arg, Datum d);
static PyObject *PLyLong_FromInt64(PLyDatumToOb *arg, Datum d);
--- 36,42 ----
static PyObject *PLyBool_FromBool(PLyDatumToOb *arg, Datum d);
static PyObject *PLyFloat_FromFloat4(PLyDatumToOb *arg, Datum d);
static PyObject *PLyFloat_FromFloat8(PLyDatumToOb *arg, Datum d);
! static PyObject *PLyDecimal_FromNumeric(PLyDatumToOb *arg, Datum d);
static PyObject *PLyInt_FromInt16(PLyDatumToOb *arg, Datum d);
static PyObject *PLyInt_FromInt32(PLyDatumToOb *arg, Datum d);
static PyObject *PLyLong_FromInt64(PLyDatumToOb *arg, Datum d);
*************** PLy_input_datum_func2(PLyDatumToOb *arg,
*** 450,456 ****
arg->func = PLyFloat_FromFloat8;
break;
case NUMERICOID:
! arg->func = PLyFloat_FromNumeric;
break;
case INT2OID:
arg->func = PLyInt_FromInt16;
--- 451,457 ----
arg->func = PLyFloat_FromFloat8;
break;
case NUMERICOID:
! arg->func = PLyDecimal_FromNumeric;
break;
case INT2OID:
arg->func = PLyInt_FromInt16;
*************** PLyFloat_FromFloat8(PLyDatumToOb *arg, D
*** 516,531 ****
}
static PyObject *
! PLyFloat_FromNumeric(PLyDatumToOb *arg, Datum d)
{
! /*
! * Numeric is cast to a PyFloat: This results in a loss of precision Would
! * it be better to cast to PyString?
! */
! Datum f = DirectFunctionCall1(numeric_float8, d);
! double x = DatumGetFloat8(f);
! return PyFloat_FromDouble(x);
}
static PyObject *
--- 517,549 ----
}
static PyObject *
! PLyDecimal_FromNumeric(PLyDatumToOb *arg, Datum d)
{
! char *x;
! PyObject *pvalue, *value, *decimal, *decimal_dict;
! static PyObject *decimal_ctor;
! /* Try to import cdecimal, if it doesnt exist, fallback to decimal */
! if (decimal_ctor == NULL)
! {
! decimal = PyImport_ImportModule("cdecimal");
! if (decimal == NULL)
! {
! PyErr_Clear();
! decimal = PyImport_ImportModule("decimal");
! }
! if (decimal == NULL)
! PLy_elog(ERROR, "could not import module 'decimal'");
!
! decimal_dict = PyModule_GetDict(decimal);
! decimal_ctor = PyDict_GetItemString(decimal_dict, "Decimal");
! Py_DECREF(decimal_dict);
! }
!
! x = DatumGetCString(DirectFunctionCall1(numeric_out, d));
! pvalue = PyString_FromString(x);
! value = PyObject_CallFunctionObjArgs(decimal_ctor, pvalue, NULL);
! return value;
}
static PyObject *
diff --git a/src/pl/plpython/sql/plpython_types.sql b/src/pl/plpython/sql/plpython_types.sql
new file mode 100644
index 6a50b42..83a8ef9
*** a/src/pl/plpython/sql/plpython_types.sql
--- b/src/pl/plpython/sql/plpython_types.sql
*************** SELECT * FROM test_type_conversion_int8(
*** 86,99 ****
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
! plpy.info(x, type(x))
return x
$$ LANGUAGE plpythonu;
! /* The current implementation converts numeric to float. */
SELECT * FROM test_type_conversion_numeric(100);
SELECT * FROM test_type_conversion_numeric(-100);
SELECT * FROM test_type_conversion_numeric(5000000000.5);
SELECT * FROM test_type_conversion_numeric(null);
--- 86,101 ----
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
! plpy.info(str(x))
return x
$$ LANGUAGE plpythonu;
! /* The current implementation converts numeric to decimal.Decimal. */
SELECT * FROM test_type_conversion_numeric(100);
SELECT * FROM test_type_conversion_numeric(-100);
SELECT * FROM test_type_conversion_numeric(5000000000.5);
+ SELECT * FROM test_type_conversion_numeric(1234567890.0987654321);
+ SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321);
SELECT * FROM test_type_conversion_numeric(null);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
