From 7919398bac8bacd75ec5d763ce8b15ffaaa3e071 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 5 Jul 2013 22:41:25 -0400 Subject: [PATCH] PL/Python: Convert numeric to Decimal The old implementation converted PostgreSQL numeric to Python float, which was always considered a shortcoming. Now numeric is converted to the Python Decimal object. Either the external cdecimal module or the standard library decimal module are supported. From: Szymon Guz From: Ronan Dunklau Reviewed-by: Steve Singer --- doc/src/sgml/plpython.sgml | 23 ++++++--- src/pl/plpython/expected/plpython_types.out | 49 ++++++++++++++++--- src/pl/plpython/expected/plpython_types_3.out | 49 ++++++++++++++++--- src/pl/plpython/plpy_typeio.c | 42 ++++++++++++---- src/pl/plpython/sql/plpython_types.sql | 9 +++- 5 files changed, 138 insertions(+), 34 deletions(-) diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index aaf758d495..ad89355d60 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -310,12 +310,23 @@ $$ LANGUAGE plpythonu; - PostgreSQL real, double, - and numeric are converted to - Python float. Note that for - the numeric this loses information and can lead to - incorrect results. This might be fixed in a future - release. + PostgreSQL real and double are converted to + Python float. + + + + + + PostgreSQL numeric is converted to + Python Decimal. This type is imported from + the cdecimal package if that is available. + Otherwise, + decimal.Decimal from the standard library will be + used. cdecimal is significantly faster + than decimal. In Python 3.3, + however, cdecimal has been integrated into the + standard library under the name decimal, so there is + no longer any difference. diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out index 46413455c8..edc51423e9 100644 --- a/src/pl/plpython/expected/plpython_types.out +++ b/src/pl/plpython/expected/plpython_types.out @@ -213,36 +213,69 @@ CONTEXT: PL/Python function "test_type_conversion_int8" (1 row) CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$ -plpy.info(x, type(x)) +# print just the class name, not the type, to avoid differences +# between decimal and cdecimal +plpy.info(x, x.__class__.__name__) return x $$ LANGUAGE plpythonu; -/* The current implementation converts numeric to float. */ SELECT * FROM test_type_conversion_numeric(100); -INFO: (100.0, ) +INFO: (Decimal('100'), 'Decimal') CONTEXT: PL/Python function "test_type_conversion_numeric" test_type_conversion_numeric ------------------------------ - 100.0 + 100 (1 row) SELECT * FROM test_type_conversion_numeric(-100); -INFO: (-100.0, ) +INFO: (Decimal('-100'), 'Decimal') +CONTEXT: PL/Python function "test_type_conversion_numeric" + test_type_conversion_numeric +------------------------------ + -100 +(1 row) + +SELECT * FROM test_type_conversion_numeric(100.0); +INFO: (Decimal('100.0'), 'Decimal') CONTEXT: PL/Python function "test_type_conversion_numeric" test_type_conversion_numeric ------------------------------ - -100.0 + 100.0 +(1 row) + +SELECT * FROM test_type_conversion_numeric(100.00); +INFO: (Decimal('100.00'), 'Decimal') +CONTEXT: PL/Python function "test_type_conversion_numeric" + test_type_conversion_numeric +------------------------------ + 100.00 (1 row) SELECT * FROM test_type_conversion_numeric(5000000000.5); -INFO: (5000000000.5, ) +INFO: (Decimal('5000000000.5'), 'Decimal') 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: (Decimal('1234567890.0987654321'), 'Decimal') +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: (Decimal('-1234567890.0987654321'), 'Decimal') +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, ) +INFO: (None, 'NoneType') 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 index 511ef5a4c9..11c4c478c4 100644 --- a/src/pl/plpython/expected/plpython_types_3.out +++ b/src/pl/plpython/expected/plpython_types_3.out @@ -213,36 +213,69 @@ CONTEXT: PL/Python function "test_type_conversion_int8" (1 row) CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$ -plpy.info(x, type(x)) +# print just the class name, not the type, to avoid differences +# between decimal and cdecimal +plpy.info(x, x.__class__.__name__) return x $$ LANGUAGE plpython3u; -/* The current implementation converts numeric to float. */ SELECT * FROM test_type_conversion_numeric(100); -INFO: (100.0, ) +INFO: (Decimal('100'), 'Decimal') CONTEXT: PL/Python function "test_type_conversion_numeric" test_type_conversion_numeric ------------------------------ - 100.0 + 100 (1 row) SELECT * FROM test_type_conversion_numeric(-100); -INFO: (-100.0, ) +INFO: (Decimal('-100'), 'Decimal') +CONTEXT: PL/Python function "test_type_conversion_numeric" + test_type_conversion_numeric +------------------------------ + -100 +(1 row) + +SELECT * FROM test_type_conversion_numeric(100.0); +INFO: (Decimal('100.0'), 'Decimal') CONTEXT: PL/Python function "test_type_conversion_numeric" test_type_conversion_numeric ------------------------------ - -100.0 + 100.0 +(1 row) + +SELECT * FROM test_type_conversion_numeric(100.00); +INFO: (Decimal('100.00'), 'Decimal') +CONTEXT: PL/Python function "test_type_conversion_numeric" + test_type_conversion_numeric +------------------------------ + 100.00 (1 row) SELECT * FROM test_type_conversion_numeric(5000000000.5); -INFO: (5000000000.5, ) +INFO: (Decimal('5000000000.5'), 'Decimal') 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: (Decimal('1234567890.0987654321'), 'Decimal') +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: (Decimal('-1234567890.0987654321'), 'Decimal') +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, ) +INFO: (None, 'NoneType') 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 index 6a9a2cb974..caccbf9b88 100644 --- a/src/pl/plpython/plpy_typeio.c +++ b/src/pl/plpython/plpy_typeio.c @@ -16,6 +16,7 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/numeric.h" #include "utils/syscache.h" #include "utils/typcache.h" @@ -35,7 +36,7 @@ static void PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup); 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 *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); @@ -450,7 +451,7 @@ PLy_input_datum_func2(PLyDatumToOb *arg, Oid typeOid, HeapTuple typeTup) arg->func = PLyFloat_FromFloat8; break; case NUMERICOID: - arg->func = PLyFloat_FromNumeric; + arg->func = PLyDecimal_FromNumeric; break; case INT2OID: arg->func = PLyInt_FromInt16; @@ -516,16 +517,37 @@ PLyFloat_FromFloat8(PLyDatumToOb *arg, Datum d) } static PyObject * -PLyFloat_FromNumeric(PLyDatumToOb *arg, Datum d) +PLyDecimal_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); + static PyObject *decimal_constructor; + char *str; + PyObject *pyvalue; + + /* Try to import cdecimal. If it doesn't exist, fall back to decimal. */ + if (!decimal_constructor) + { + PyObject *decimal_module; + + decimal_module = PyImport_ImportModule("cdecimal"); + if (!decimal_module) + { + PyErr_Clear(); + decimal_module = PyImport_ImportModule("decimal"); + } + if (!decimal_module) + PLy_elog(ERROR, "could not import a module for Decimal constructor"); + + decimal_constructor = PyObject_GetAttrString(decimal_module, "Decimal"); + if (!decimal_constructor) + PLy_elog(ERROR, "no Decimal attribute in module"); + } + + str = DatumGetCString(DirectFunctionCall1(numeric_out, d)); + pyvalue = PyObject_CallFunction(decimal_constructor, "s", str); + if (!pyvalue) + PLy_elog(ERROR, "conversion from numeric to Decimal failed"); - return PyFloat_FromDouble(x); + return pyvalue; } static PyObject * diff --git a/src/pl/plpython/sql/plpython_types.sql b/src/pl/plpython/sql/plpython_types.sql index 6a50b4236d..6881880729 100644 --- a/src/pl/plpython/sql/plpython_types.sql +++ b/src/pl/plpython/sql/plpython_types.sql @@ -86,14 +86,19 @@ SELECT * FROM test_type_conversion_int8(null); CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$ -plpy.info(x, type(x)) +# print just the class name, not the type, to avoid differences +# between decimal and cdecimal +plpy.info(x, x.__class__.__name__) 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(100.0); +SELECT * FROM test_type_conversion_numeric(100.00); 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); -- 2.40.0