From 12c2f2f66c2d37a951cdf01049d74c520f26e7f9 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 18 Mar 2010 13:23:57 +0000 Subject: [PATCH] Use data-type specific conversion functions also in plpy.execute In PLy_spi_execute_plan, use the data-type specific Python-to-PostgreSQL conversion function instead of passing everything through InputFunctionCall as a string. The equivalent fix was already done months ago for function parameters and return values, but this other gateway between Python and PostgreSQL was apparently forgotten. As a result, data types that need special treatment, such as bytea, would misbehave when used with plpy.execute. --- src/pl/plpython/expected/plpython_types.out | 61 +++++++++ src/pl/plpython/expected/plpython_types_3.out | 119 +++++++++++++----- src/pl/plpython/plpython.c | 45 +------ src/pl/plpython/sql/plpython_types.sql | 50 ++++++++ 4 files changed, 206 insertions(+), 69 deletions(-) diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out index 9cda31b13d..502dbb5cc0 100644 --- a/src/pl/plpython/expected/plpython_types.out +++ b/src/pl/plpython/expected/plpython_types.out @@ -587,3 +587,64 @@ SELECT * FROM test_type_conversion_array_error(); ERROR: PL/Python: return value of function with array return type is not a Python sequence CONTEXT: while creating return value PL/Python function "test_type_conversion_array_error" +-- +-- Prepared statements +-- +CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean']) +rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python +return rv[0]['val'] +$$; +SELECT test_prep_bool_input(); -- 1 + test_prep_bool_input +---------------------- + 1 +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT $1 = 1 AS val", ['int']) +rv = plpy.execute(plan, [0], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; +SELECT test_prep_bool_output(); -- false +INFO: {'val': False} +CONTEXT: PL/Python function "test_prep_bool_output" + test_prep_bool_output +----------------------- + f +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea']) +rv = plpy.execute(plan, [bb], 5) +return rv[0]['val'] +$$; +SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value) + test_prep_bytea_input +----------------------- + 3 +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val") +rv = plpy.execute(plan, [], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; +SELECT test_prep_bytea_output(); +INFO: {'val': '\xaa\x00\xbb'} +CONTEXT: PL/Python function "test_prep_bytea_output" + test_prep_bytea_output +------------------------ + \xaa00bb +(1 row) + diff --git a/src/pl/plpython/expected/plpython_types_3.out b/src/pl/plpython/expected/plpython_types_3.out index 297a0f8af3..d88495512c 100644 --- a/src/pl/plpython/expected/plpython_types_3.out +++ b/src/pl/plpython/expected/plpython_types_3.out @@ -7,7 +7,7 @@ CREATE FUNCTION test_type_conversion_bool(x bool) RETURNS bool AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bool(true); INFO: (True, ) CONTEXT: PL/Python function "test_type_conversion_bool" @@ -51,7 +51,7 @@ elif n == 5: ret = [0] plpy.info(ret, not not ret) return ret -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bool_other(0); INFO: (0, False) CONTEXT: PL/Python function "test_type_conversion_bool_other" @@ -103,7 +103,7 @@ CONTEXT: PL/Python function "test_type_conversion_bool_other" CREATE FUNCTION test_type_conversion_char(x char) RETURNS char AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_char('a'); INFO: ('a', ) CONTEXT: PL/Python function "test_type_conversion_char" @@ -123,7 +123,7 @@ CONTEXT: PL/Python function "test_type_conversion_char" CREATE FUNCTION test_type_conversion_int2(x int2) RETURNS int2 AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_int2(100::int2); INFO: (100, ) CONTEXT: PL/Python function "test_type_conversion_int2" @@ -151,7 +151,7 @@ CONTEXT: PL/Python function "test_type_conversion_int2" CREATE FUNCTION test_type_conversion_int4(x int4) RETURNS int4 AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_int4(100); INFO: (100, ) CONTEXT: PL/Python function "test_type_conversion_int4" @@ -179,9 +179,9 @@ CONTEXT: PL/Python function "test_type_conversion_int4" CREATE FUNCTION test_type_conversion_int8(x int8) RETURNS int8 AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_int8(100); -INFO: (100L, ) +INFO: (100, ) CONTEXT: PL/Python function "test_type_conversion_int8" test_type_conversion_int8 --------------------------- @@ -189,7 +189,7 @@ CONTEXT: PL/Python function "test_type_conversion_int8" (1 row) SELECT * FROM test_type_conversion_int8(-100); -INFO: (-100L, ) +INFO: (-100, ) CONTEXT: PL/Python function "test_type_conversion_int8" test_type_conversion_int8 --------------------------- @@ -197,7 +197,7 @@ CONTEXT: PL/Python function "test_type_conversion_int8" (1 row) SELECT * FROM test_type_conversion_int8(5000000000); -INFO: (5000000000L, ) +INFO: (5000000000, ) CONTEXT: PL/Python function "test_type_conversion_int8" test_type_conversion_int8 --------------------------- @@ -215,7 +215,7 @@ CONTEXT: PL/Python function "test_type_conversion_int8" CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; /* The current implementation converts numeric to float. */ SELECT * FROM test_type_conversion_numeric(100); INFO: (100.0, ) @@ -252,7 +252,7 @@ CONTEXT: PL/Python function "test_type_conversion_numeric" CREATE FUNCTION test_type_conversion_float4(x float4) RETURNS float4 AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_float4(100); INFO: (100.0, ) CONTEXT: PL/Python function "test_type_conversion_float4" @@ -288,7 +288,7 @@ CONTEXT: PL/Python function "test_type_conversion_float4" CREATE FUNCTION test_type_conversion_float8(x float8) RETURNS float8 AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_float8(100); INFO: (100.0, ) CONTEXT: PL/Python function "test_type_conversion_float8" @@ -324,7 +324,7 @@ CONTEXT: PL/Python function "test_type_conversion_float8" CREATE FUNCTION test_type_conversion_text(x text) RETURNS text AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_text('hello world'); INFO: ('hello world', ) CONTEXT: PL/Python function "test_type_conversion_text" @@ -344,7 +344,7 @@ CONTEXT: PL/Python function "test_type_conversion_text" CREATE FUNCTION test_type_conversion_bytea(x bytea) RETURNS bytea AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bytea('hello world'); INFO: (b'hello world', ) CONTEXT: PL/Python function "test_type_conversion_bytea" @@ -372,14 +372,14 @@ CONTEXT: PL/Python function "test_type_conversion_bytea" CREATE FUNCTION test_type_marshal() RETURNS bytea AS $$ import marshal return marshal.dumps('hello world') -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; CREATE FUNCTION test_type_unmarshal(x bytea) RETURNS text AS $$ import marshal try: return marshal.loads(x) -except ValueError, e: +except ValueError as e: return 'FAILED: ' + str(e) -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT test_type_unmarshal(x) FROM test_type_marshal() x; test_type_unmarshal --------------------- @@ -392,7 +392,7 @@ SELECT test_type_unmarshal(x) FROM test_type_marshal() x; CREATE DOMAIN booltrue AS bool CHECK (VALUE IS TRUE OR VALUE IS NULL); CREATE FUNCTION test_type_conversion_booltrue(x booltrue, y bool) RETURNS booltrue AS $$ return y -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_booltrue(true, true); test_type_conversion_booltrue ------------------------------- @@ -409,7 +409,7 @@ CREATE DOMAIN uint2 AS int2 CHECK (VALUE >= 0); CREATE FUNCTION test_type_conversion_uint2(x uint2, y int) RETURNS uint2 AS $$ plpy.info(x, type(x)) return y -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_uint2(100::uint2, 50); INFO: (100, ) CONTEXT: PL/Python function "test_type_conversion_uint2" @@ -435,7 +435,7 @@ CONTEXT: PL/Python function "test_type_conversion_uint2" CREATE DOMAIN nnint AS int CHECK (VALUE IS NOT NULL); CREATE FUNCTION test_type_conversion_nnint(x nnint, y int) RETURNS nnint AS $$ return y -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_nnint(10, 20); test_type_conversion_nnint ---------------------------- @@ -452,7 +452,7 @@ CREATE DOMAIN bytea10 AS bytea CHECK (octet_length(VALUE) = 10 AND VALUE IS NOT CREATE FUNCTION test_type_conversion_bytea10(x bytea10, y bytea) RETURNS bytea10 AS $$ plpy.info(x, type(x)) return y -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bytea10('hello wold', 'hello wold'); INFO: (b'hello wold', ) CONTEXT: PL/Python function "test_type_conversion_bytea10" @@ -483,7 +483,7 @@ PL/Python function "test_type_conversion_bytea10" CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]); INFO: ([0, 100], ) CONTEXT: PL/Python function "test_type_conversion_array_int4" @@ -531,7 +531,7 @@ CONTEXT: PL/Python function "test_type_conversion_array_int4" CREATE FUNCTION test_type_conversion_array_bytea(x bytea[]) RETURNS bytea[] AS $$ plpy.info(x, type(x)) return x -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_bytea(ARRAY[E'\\xdeadbeef'::bytea, NULL]); INFO: ([b'\xde\xad\xbe\xef', None], ) CONTEXT: PL/Python function "test_type_conversion_array_bytea" @@ -542,7 +542,7 @@ CONTEXT: PL/Python function "test_type_conversion_array_bytea" CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$ return [123, 'abc'] -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_mixed1(); test_type_conversion_array_mixed1 ----------------------------------- @@ -551,20 +551,20 @@ SELECT * FROM test_type_conversion_array_mixed1(); CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$ return [123, 'abc'] -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_mixed2(); ERROR: invalid input syntax for integer: "abc" CONTEXT: while creating return value PL/Python function "test_type_conversion_array_mixed2" CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$ return [None] -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_record(); ERROR: PL/Python functions cannot return type type_record[] DETAIL: PL/Python does not support conversion to arrays of row types. CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$ return 'abc' -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_string(); test_type_conversion_array_string ----------------------------------- @@ -573,7 +573,7 @@ SELECT * FROM test_type_conversion_array_string(); CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$ return ('abc', 'def') -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_tuple(); test_type_conversion_array_tuple ---------------------------------- @@ -582,8 +582,69 @@ SELECT * FROM test_type_conversion_array_tuple(); CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$ return 5 -$$ LANGUAGE plpythonu; +$$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_error(); ERROR: PL/Python: return value of function with array return type is not a Python sequence CONTEXT: while creating return value PL/Python function "test_type_conversion_array_error" +-- +-- Prepared statements +-- +CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean']) +rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python +return rv[0]['val'] +$$; +SELECT test_prep_bool_input(); -- 1 + test_prep_bool_input +---------------------- + 1 +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT $1 = 1 AS val", ['int']) +rv = plpy.execute(plan, [0], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; +SELECT test_prep_bool_output(); -- false +INFO: {'val': False} +CONTEXT: PL/Python function "test_prep_bool_output" + test_prep_bool_output +----------------------- + f +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea']) +rv = plpy.execute(plan, [bb], 5) +return rv[0]['val'] +$$; +SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value) + test_prep_bytea_input +----------------------- + 3 +(1 row) + +CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea +LANGUAGE plpython3u +AS $$ +plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val") +rv = plpy.execute(plan, [], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; +SELECT test_prep_bytea_output(); +INFO: {'val': b'\xaa\x00\xbb'} +CONTEXT: PL/Python function "test_prep_bytea_output" + test_prep_bytea_output +------------------------ + \xaa00bb +(1 row) + diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c index c9adb533f7..6b5a56e0c7 100644 --- a/src/pl/plpython/plpython.c +++ b/src/pl/plpython/plpython.c @@ -1,7 +1,7 @@ /********************************************************************** * plpython.c - python as a procedural language for PostgreSQL * - * $PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.139 2010/02/26 02:01:36 momjian Exp $ + * $PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.140 2010/03/18 13:23:56 petere Exp $ * ********************************************************************* */ @@ -287,7 +287,6 @@ static void *PLy_malloc0(size_t); static char *PLy_strdup(const char *); static void PLy_free(void *); -static PyObject *PLyUnicode_Str(PyObject *unicode); static PyObject *PLyUnicode_Bytes(PyObject *unicode); static char *PLyUnicode_AsString(PyObject *unicode); @@ -2983,38 +2982,24 @@ PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit) for (j = 0; j < nargs; j++) { - PyObject *elem, - *so; + PyObject *elem; elem = PySequence_GetItem(list, j); if (elem != Py_None) { - if (PyUnicode_Check(elem)) - so = PLyUnicode_Str(elem); - else - so = PyObject_Str(elem); - if (!so) - PLy_elog(ERROR, "could not execute plan"); - Py_DECREF(elem); - PG_TRY(); { - char *sv = PyString_AsString(so); - plan->values[j] = - InputFunctionCall(&(plan->args[j].out.d.typfunc), - sv, - plan->args[j].out.d.typioparam, - -1); + plan->args[j].out.d.func(NULL, &(plan->args[j].out.d), elem); } PG_CATCH(); { - Py_DECREF(so); + Py_DECREF(elem); PG_RE_THROW(); } PG_END_TRY(); - Py_DECREF(so); + Py_DECREF(elem); nulls[j] = ' '; } else @@ -3637,26 +3622,6 @@ PLy_free(void *ptr) free(ptr); } -/* - * Convert a Unicode object to a Python string. - */ -static PyObject * -PLyUnicode_Str(PyObject *unicode) -{ -#if PY_MAJOR_VERSION >= 3 - /* In Python 3, this is a noop. */ - Py_INCREF(unicode); - return unicode; -#else - - /* - * In Python 2, this means converting the Unicode to bytes in the server - * encoding. - */ - return PLyUnicode_Bytes(unicode); -#endif -} - /* * Convert a Python unicode object to a Python string/bytes object in * PostgreSQL server encoding. Reference ownership is passed to the diff --git a/src/pl/plpython/sql/plpython_types.sql b/src/pl/plpython/sql/plpython_types.sql index 2afbc87058..0b905d1802 100644 --- a/src/pl/plpython/sql/plpython_types.sql +++ b/src/pl/plpython/sql/plpython_types.sql @@ -269,3 +269,53 @@ return 5 $$ LANGUAGE plpythonu; SELECT * FROM test_type_conversion_array_error(); + + +-- +-- Prepared statements +-- + +CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean']) +rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python +return rv[0]['val'] +$$; + +SELECT test_prep_bool_input(); -- 1 + + +CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT $1 = 1 AS val", ['int']) +rv = plpy.execute(plan, [0], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; + +SELECT test_prep_bool_output(); -- false + + +CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea']) +rv = plpy.execute(plan, [bb], 5) +return rv[0]['val'] +$$; + +SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value) + + +CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea +LANGUAGE plpythonu +AS $$ +plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val") +rv = plpy.execute(plan, [], 5) +plpy.info(rv[0]) +return rv[0]['val'] +$$; + +SELECT test_prep_bytea_output(); -- 2.40.0