Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7919398

Browse files
committed
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 tothe Python Decimal object. Either the external cdecimal module or thestandard library decimal module are supported.From: Szymon Guz <mabewlun@gmail.com>From: Ronan Dunklau <rdunklau@gmail.com>Reviewed-by: Steve Singer <steve@ssinger.info>
1 parent02d2b69 commit7919398

File tree

5 files changed

+138
-34
lines changed

5 files changed

+138
-34
lines changed

‎doc/src/sgml/plpython.sgml

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -310,12 +310,23 @@ $$ LANGUAGE plpythonu;
310310

311311
<listitem>
312312
<para>
313-
PostgreSQL <type>real</type>, <type>double</type>,
314-
and <type>numeric</type> are converted to
315-
Python <type>float</type>. Note that for
316-
the <type>numeric</type> this loses information and can lead to
317-
incorrect results. This might be fixed in a future
318-
release.
313+
PostgreSQL <type>real</type> and <type>double</type> are converted to
314+
Python <type>float</type>.
315+
</para>
316+
</listitem>
317+
318+
<listitem>
319+
<para>
320+
PostgreSQL <type>numeric</type> is converted to
321+
Python <type>Decimal</type>. This type is imported from
322+
the <literal>cdecimal</literal> package if that is available.
323+
Otherwise,
324+
<literal>decimal.Decimal</literal> from the standard library will be
325+
used. <literal>cdecimal</literal> is significantly faster
326+
than <literal>decimal</literal>. In Python 3.3,
327+
however, <literal>cdecimal</literal> has been integrated into the
328+
standard library under the name <literal>decimal</literal>, so there is
329+
no longer any difference.
319330
</para>
320331
</listitem>
321332

‎src/pl/plpython/expected/plpython_types.out

Lines changed: 41 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -213,36 +213,69 @@ CONTEXT: PL/Python function "test_type_conversion_int8"
213213
(1 row)
214214

215215
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
216-
plpy.info(x, type(x))
216+
# print just the class name, not the type, to avoid differences
217+
# between decimal and cdecimal
218+
plpy.info(x, x.__class__.__name__)
217219
return x
218220
$$ LANGUAGE plpythonu;
219-
/* The current implementation converts numeric to float. */
220221
SELECT * FROM test_type_conversion_numeric(100);
221-
INFO: (100.0, <type 'float'>)
222+
INFO: (Decimal('100'), 'Decimal')
222223
CONTEXT: PL/Python function "test_type_conversion_numeric"
223224
test_type_conversion_numeric
224225
------------------------------
225-
100.0
226+
100
226227
(1 row)
227228

228229
SELECT * FROM test_type_conversion_numeric(-100);
229-
INFO: (-100.0, <type 'float'>)
230+
INFO: (Decimal('-100'), 'Decimal')
231+
CONTEXT: PL/Python function "test_type_conversion_numeric"
232+
test_type_conversion_numeric
233+
------------------------------
234+
-100
235+
(1 row)
236+
237+
SELECT * FROM test_type_conversion_numeric(100.0);
238+
INFO: (Decimal('100.0'), 'Decimal')
230239
CONTEXT: PL/Python function "test_type_conversion_numeric"
231240
test_type_conversion_numeric
232241
------------------------------
233-
-100.0
242+
100.0
243+
(1 row)
244+
245+
SELECT * FROM test_type_conversion_numeric(100.00);
246+
INFO: (Decimal('100.00'), 'Decimal')
247+
CONTEXT: PL/Python function "test_type_conversion_numeric"
248+
test_type_conversion_numeric
249+
------------------------------
250+
100.00
234251
(1 row)
235252

236253
SELECT * FROM test_type_conversion_numeric(5000000000.5);
237-
INFO: (5000000000.5, <type 'float'>)
254+
INFO: (Decimal('5000000000.5'), 'Decimal')
238255
CONTEXT: PL/Python function "test_type_conversion_numeric"
239256
test_type_conversion_numeric
240257
------------------------------
241258
5000000000.5
242259
(1 row)
243260

261+
SELECT * FROM test_type_conversion_numeric(1234567890.0987654321);
262+
INFO: (Decimal('1234567890.0987654321'), 'Decimal')
263+
CONTEXT: PL/Python function "test_type_conversion_numeric"
264+
test_type_conversion_numeric
265+
------------------------------
266+
1234567890.0987654321
267+
(1 row)
268+
269+
SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321);
270+
INFO: (Decimal('-1234567890.0987654321'), 'Decimal')
271+
CONTEXT: PL/Python function "test_type_conversion_numeric"
272+
test_type_conversion_numeric
273+
------------------------------
274+
-1234567890.0987654321
275+
(1 row)
276+
244277
SELECT * FROM test_type_conversion_numeric(null);
245-
INFO: (None,<type'NoneType'>)
278+
INFO: (None, 'NoneType')
246279
CONTEXT: PL/Python function "test_type_conversion_numeric"
247280
test_type_conversion_numeric
248281
------------------------------

‎src/pl/plpython/expected/plpython_types_3.out

Lines changed: 41 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -213,36 +213,69 @@ CONTEXT: PL/Python function "test_type_conversion_int8"
213213
(1 row)
214214

215215
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
216-
plpy.info(x, type(x))
216+
# print just the class name, not the type, to avoid differences
217+
# between decimal and cdecimal
218+
plpy.info(x, x.__class__.__name__)
217219
return x
218220
$$ LANGUAGE plpython3u;
219-
/* The current implementation converts numeric to float. */
220221
SELECT * FROM test_type_conversion_numeric(100);
221-
INFO: (100.0, <class 'float'>)
222+
INFO: (Decimal('100'), 'Decimal')
222223
CONTEXT: PL/Python function "test_type_conversion_numeric"
223224
test_type_conversion_numeric
224225
------------------------------
225-
100.0
226+
100
226227
(1 row)
227228

228229
SELECT * FROM test_type_conversion_numeric(-100);
229-
INFO: (-100.0, <class 'float'>)
230+
INFO: (Decimal('-100'), 'Decimal')
231+
CONTEXT: PL/Python function "test_type_conversion_numeric"
232+
test_type_conversion_numeric
233+
------------------------------
234+
-100
235+
(1 row)
236+
237+
SELECT * FROM test_type_conversion_numeric(100.0);
238+
INFO: (Decimal('100.0'), 'Decimal')
230239
CONTEXT: PL/Python function "test_type_conversion_numeric"
231240
test_type_conversion_numeric
232241
------------------------------
233-
-100.0
242+
100.0
243+
(1 row)
244+
245+
SELECT * FROM test_type_conversion_numeric(100.00);
246+
INFO: (Decimal('100.00'), 'Decimal')
247+
CONTEXT: PL/Python function "test_type_conversion_numeric"
248+
test_type_conversion_numeric
249+
------------------------------
250+
100.00
234251
(1 row)
235252

236253
SELECT * FROM test_type_conversion_numeric(5000000000.5);
237-
INFO: (5000000000.5, <class 'float'>)
254+
INFO: (Decimal('5000000000.5'), 'Decimal')
238255
CONTEXT: PL/Python function "test_type_conversion_numeric"
239256
test_type_conversion_numeric
240257
------------------------------
241258
5000000000.5
242259
(1 row)
243260

261+
SELECT * FROM test_type_conversion_numeric(1234567890.0987654321);
262+
INFO: (Decimal('1234567890.0987654321'), 'Decimal')
263+
CONTEXT: PL/Python function "test_type_conversion_numeric"
264+
test_type_conversion_numeric
265+
------------------------------
266+
1234567890.0987654321
267+
(1 row)
268+
269+
SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321);
270+
INFO: (Decimal('-1234567890.0987654321'), 'Decimal')
271+
CONTEXT: PL/Python function "test_type_conversion_numeric"
272+
test_type_conversion_numeric
273+
------------------------------
274+
-1234567890.0987654321
275+
(1 row)
276+
244277
SELECT * FROM test_type_conversion_numeric(null);
245-
INFO: (None,<class'NoneType'>)
278+
INFO: (None, 'NoneType')
246279
CONTEXT: PL/Python function "test_type_conversion_numeric"
247280
test_type_conversion_numeric
248281
------------------------------

‎src/pl/plpython/plpy_typeio.c

Lines changed: 32 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616
#include"utils/builtins.h"
1717
#include"utils/lsyscache.h"
1818
#include"utils/memutils.h"
19+
#include"utils/numeric.h"
1920
#include"utils/syscache.h"
2021
#include"utils/typcache.h"
2122

@@ -35,7 +36,7 @@ static void PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup);
3536
staticPyObject*PLyBool_FromBool(PLyDatumToOb*arg,Datumd);
3637
staticPyObject*PLyFloat_FromFloat4(PLyDatumToOb*arg,Datumd);
3738
staticPyObject*PLyFloat_FromFloat8(PLyDatumToOb*arg,Datumd);
38-
staticPyObject*PLyFloat_FromNumeric(PLyDatumToOb*arg,Datumd);
39+
staticPyObject*PLyDecimal_FromNumeric(PLyDatumToOb*arg,Datumd);
3940
staticPyObject*PLyInt_FromInt16(PLyDatumToOb*arg,Datumd);
4041
staticPyObject*PLyInt_FromInt32(PLyDatumToOb*arg,Datumd);
4142
staticPyObject*PLyLong_FromInt64(PLyDatumToOb*arg,Datumd);
@@ -450,7 +451,7 @@ PLy_input_datum_func2(PLyDatumToOb *arg, Oid typeOid, HeapTuple typeTup)
450451
arg->func=PLyFloat_FromFloat8;
451452
break;
452453
caseNUMERICOID:
453-
arg->func=PLyFloat_FromNumeric;
454+
arg->func=PLyDecimal_FromNumeric;
454455
break;
455456
caseINT2OID:
456457
arg->func=PLyInt_FromInt16;
@@ -516,16 +517,37 @@ PLyFloat_FromFloat8(PLyDatumToOb *arg, Datum d)
516517
}
517518

518519
staticPyObject*
519-
PLyFloat_FromNumeric(PLyDatumToOb*arg,Datumd)
520+
PLyDecimal_FromNumeric(PLyDatumToOb*arg,Datumd)
520521
{
521-
/*
522-
* Numeric is cast to a PyFloat: This results in a loss of precision Would
523-
* it be better to cast to PyString?
524-
*/
525-
Datumf=DirectFunctionCall1(numeric_float8,d);
526-
doublex=DatumGetFloat8(f);
522+
staticPyObject*decimal_constructor;
523+
char*str;
524+
PyObject*pyvalue;
525+
526+
/* Try to import cdecimal. If it doesn't exist, fall back to decimal. */
527+
if (!decimal_constructor)
528+
{
529+
PyObject*decimal_module;
530+
531+
decimal_module=PyImport_ImportModule("cdecimal");
532+
if (!decimal_module)
533+
{
534+
PyErr_Clear();
535+
decimal_module=PyImport_ImportModule("decimal");
536+
}
537+
if (!decimal_module)
538+
PLy_elog(ERROR,"could not import a module for Decimal constructor");
539+
540+
decimal_constructor=PyObject_GetAttrString(decimal_module,"Decimal");
541+
if (!decimal_constructor)
542+
PLy_elog(ERROR,"no Decimal attribute in module");
543+
}
544+
545+
str=DatumGetCString(DirectFunctionCall1(numeric_out,d));
546+
pyvalue=PyObject_CallFunction(decimal_constructor,"s",str);
547+
if (!pyvalue)
548+
PLy_elog(ERROR,"conversion from numeric to Decimal failed");
527549

528-
returnPyFloat_FromDouble(x);
550+
returnpyvalue;
529551
}
530552

531553
staticPyObject*

‎src/pl/plpython/sql/plpython_types.sql

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -86,14 +86,19 @@ SELECT * FROM test_type_conversion_int8(null);
8686

8787

8888
CREATEFUNCTIONtest_type_conversion_numeric(xnumeric) RETURNSnumericAS $$
89-
plpy.info(x, type(x))
89+
# print just the class name, not the type, to avoid differences
90+
# between decimal and cdecimal
91+
plpy.info(x,x.__class__.__name__)
9092
return x
9193
$$ LANGUAGE plpythonu;
9294

93-
/* The current implementation converts numeric to float.*/
9495
SELECT*FROM test_type_conversion_numeric(100);
9596
SELECT*FROM test_type_conversion_numeric(-100);
97+
SELECT*FROM test_type_conversion_numeric(100.0);
98+
SELECT*FROM test_type_conversion_numeric(100.00);
9699
SELECT*FROM test_type_conversion_numeric(5000000000.5);
100+
SELECT*FROM test_type_conversion_numeric(1234567890.0987654321);
101+
SELECT*FROM test_type_conversion_numeric(-1234567890.0987654321);
97102
SELECT*FROM test_type_conversion_numeric(null);
98103

99104

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp