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

Commit12c2f2f

Browse files
committed
Use data-type specific conversion functions also in plpy.execute
In PLy_spi_execute_plan, use the data-type specific Python-to-PostgreSQLconversion function instead of passing everything through InputFunctionCallas a string. The equivalent fix was already done months ago for functionparameters and return values, but this other gateway between Python andPostgreSQL was apparently forgotten. As a result, data types that needspecial treatment, such as bytea, would misbehave when used withplpy.execute.
1 parentc21ac0b commit12c2f2f

File tree

4 files changed

+206
-69
lines changed

4 files changed

+206
-69
lines changed

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

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -587,3 +587,64 @@ SELECT * FROM test_type_conversion_array_error();
587587
ERROR: PL/Python: return value of function with array return type is not a Python sequence
588588
CONTEXT: while creating return value
589589
PL/Python function "test_type_conversion_array_error"
590+
--
591+
-- Prepared statements
592+
--
593+
CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int
594+
LANGUAGE plpythonu
595+
AS $$
596+
plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean'])
597+
rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python
598+
return rv[0]['val']
599+
$$;
600+
SELECT test_prep_bool_input(); -- 1
601+
test_prep_bool_input
602+
----------------------
603+
1
604+
(1 row)
605+
606+
CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool
607+
LANGUAGE plpythonu
608+
AS $$
609+
plan = plpy.prepare("SELECT $1 = 1 AS val", ['int'])
610+
rv = plpy.execute(plan, [0], 5)
611+
plpy.info(rv[0])
612+
return rv[0]['val']
613+
$$;
614+
SELECT test_prep_bool_output(); -- false
615+
INFO: {'val': False}
616+
CONTEXT: PL/Python function "test_prep_bool_output"
617+
test_prep_bool_output
618+
-----------------------
619+
f
620+
(1 row)
621+
622+
CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int
623+
LANGUAGE plpythonu
624+
AS $$
625+
plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea'])
626+
rv = plpy.execute(plan, [bb], 5)
627+
return rv[0]['val']
628+
$$;
629+
SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value)
630+
test_prep_bytea_input
631+
-----------------------
632+
3
633+
(1 row)
634+
635+
CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea
636+
LANGUAGE plpythonu
637+
AS $$
638+
plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val")
639+
rv = plpy.execute(plan, [], 5)
640+
plpy.info(rv[0])
641+
return rv[0]['val']
642+
$$;
643+
SELECT test_prep_bytea_output();
644+
INFO: {'val': '\xaa\x00\xbb'}
645+
CONTEXT: PL/Python function "test_prep_bytea_output"
646+
test_prep_bytea_output
647+
------------------------
648+
\xaa00bb
649+
(1 row)
650+

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

Lines changed: 90 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
CREATE FUNCTION test_type_conversion_bool(x bool) RETURNS bool AS $$
88
plpy.info(x, type(x))
99
return x
10-
$$ LANGUAGEplpythonu;
10+
$$ LANGUAGEplpython3u;
1111
SELECT * FROM test_type_conversion_bool(true);
1212
INFO: (True, <class 'bool'>)
1313
CONTEXT: PL/Python function "test_type_conversion_bool"
@@ -51,7 +51,7 @@ elif n == 5:
5151
ret = [0]
5252
plpy.info(ret, not not ret)
5353
return ret
54-
$$ LANGUAGEplpythonu;
54+
$$ LANGUAGEplpython3u;
5555
SELECT * FROM test_type_conversion_bool_other(0);
5656
INFO: (0, False)
5757
CONTEXT: PL/Python function "test_type_conversion_bool_other"
@@ -103,7 +103,7 @@ CONTEXT: PL/Python function "test_type_conversion_bool_other"
103103
CREATE FUNCTION test_type_conversion_char(x char) RETURNS char AS $$
104104
plpy.info(x, type(x))
105105
return x
106-
$$ LANGUAGEplpythonu;
106+
$$ LANGUAGEplpython3u;
107107
SELECT * FROM test_type_conversion_char('a');
108108
INFO: ('a', <class 'str'>)
109109
CONTEXT: PL/Python function "test_type_conversion_char"
@@ -123,7 +123,7 @@ CONTEXT: PL/Python function "test_type_conversion_char"
123123
CREATE FUNCTION test_type_conversion_int2(x int2) RETURNS int2 AS $$
124124
plpy.info(x, type(x))
125125
return x
126-
$$ LANGUAGEplpythonu;
126+
$$ LANGUAGEplpython3u;
127127
SELECT * FROM test_type_conversion_int2(100::int2);
128128
INFO: (100, <class 'int'>)
129129
CONTEXT: PL/Python function "test_type_conversion_int2"
@@ -151,7 +151,7 @@ CONTEXT: PL/Python function "test_type_conversion_int2"
151151
CREATE FUNCTION test_type_conversion_int4(x int4) RETURNS int4 AS $$
152152
plpy.info(x, type(x))
153153
return x
154-
$$ LANGUAGEplpythonu;
154+
$$ LANGUAGEplpython3u;
155155
SELECT * FROM test_type_conversion_int4(100);
156156
INFO: (100, <class 'int'>)
157157
CONTEXT: PL/Python function "test_type_conversion_int4"
@@ -179,25 +179,25 @@ CONTEXT: PL/Python function "test_type_conversion_int4"
179179
CREATE FUNCTION test_type_conversion_int8(x int8) RETURNS int8 AS $$
180180
plpy.info(x, type(x))
181181
return x
182-
$$ LANGUAGEplpythonu;
182+
$$ LANGUAGEplpython3u;
183183
SELECT * FROM test_type_conversion_int8(100);
184-
INFO: (100L, <type 'long'>)
184+
INFO: (100, <class 'int'>)
185185
CONTEXT: PL/Python function "test_type_conversion_int8"
186186
test_type_conversion_int8
187187
---------------------------
188188
100
189189
(1 row)
190190

191191
SELECT * FROM test_type_conversion_int8(-100);
192-
INFO: (-100L, <type 'long'>)
192+
INFO: (-100, <class 'int'>)
193193
CONTEXT: PL/Python function "test_type_conversion_int8"
194194
test_type_conversion_int8
195195
---------------------------
196196
-100
197197
(1 row)
198198

199199
SELECT * FROM test_type_conversion_int8(5000000000);
200-
INFO: (5000000000L, <type 'long'>)
200+
INFO: (5000000000, <class 'int'>)
201201
CONTEXT: PL/Python function "test_type_conversion_int8"
202202
test_type_conversion_int8
203203
---------------------------
@@ -215,7 +215,7 @@ CONTEXT: PL/Python function "test_type_conversion_int8"
215215
CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
216216
plpy.info(x, type(x))
217217
return x
218-
$$ LANGUAGEplpythonu;
218+
$$ LANGUAGEplpython3u;
219219
/* The current implementation converts numeric to float. */
220220
SELECT * FROM test_type_conversion_numeric(100);
221221
INFO: (100.0, <class 'float'>)
@@ -252,7 +252,7 @@ CONTEXT: PL/Python function "test_type_conversion_numeric"
252252
CREATE FUNCTION test_type_conversion_float4(x float4) RETURNS float4 AS $$
253253
plpy.info(x, type(x))
254254
return x
255-
$$ LANGUAGEplpythonu;
255+
$$ LANGUAGEplpython3u;
256256
SELECT * FROM test_type_conversion_float4(100);
257257
INFO: (100.0, <class 'float'>)
258258
CONTEXT: PL/Python function "test_type_conversion_float4"
@@ -288,7 +288,7 @@ CONTEXT: PL/Python function "test_type_conversion_float4"
288288
CREATE FUNCTION test_type_conversion_float8(x float8) RETURNS float8 AS $$
289289
plpy.info(x, type(x))
290290
return x
291-
$$ LANGUAGEplpythonu;
291+
$$ LANGUAGEplpython3u;
292292
SELECT * FROM test_type_conversion_float8(100);
293293
INFO: (100.0, <class 'float'>)
294294
CONTEXT: PL/Python function "test_type_conversion_float8"
@@ -324,7 +324,7 @@ CONTEXT: PL/Python function "test_type_conversion_float8"
324324
CREATE FUNCTION test_type_conversion_text(x text) RETURNS text AS $$
325325
plpy.info(x, type(x))
326326
return x
327-
$$ LANGUAGEplpythonu;
327+
$$ LANGUAGEplpython3u;
328328
SELECT * FROM test_type_conversion_text('hello world');
329329
INFO: ('hello world', <class 'str'>)
330330
CONTEXT: PL/Python function "test_type_conversion_text"
@@ -344,7 +344,7 @@ CONTEXT: PL/Python function "test_type_conversion_text"
344344
CREATE FUNCTION test_type_conversion_bytea(x bytea) RETURNS bytea AS $$
345345
plpy.info(x, type(x))
346346
return x
347-
$$ LANGUAGEplpythonu;
347+
$$ LANGUAGEplpython3u;
348348
SELECT * FROM test_type_conversion_bytea('hello world');
349349
INFO: (b'hello world', <class 'bytes'>)
350350
CONTEXT: PL/Python function "test_type_conversion_bytea"
@@ -372,14 +372,14 @@ CONTEXT: PL/Python function "test_type_conversion_bytea"
372372
CREATE FUNCTION test_type_marshal() RETURNS bytea AS $$
373373
import marshal
374374
return marshal.dumps('hello world')
375-
$$ LANGUAGEplpythonu;
375+
$$ LANGUAGEplpython3u;
376376
CREATE FUNCTION test_type_unmarshal(x bytea) RETURNS text AS $$
377377
import marshal
378378
try:
379379
return marshal.loads(x)
380-
except ValueError, e:
380+
except ValueError as e:
381381
return 'FAILED: ' + str(e)
382-
$$ LANGUAGEplpythonu;
382+
$$ LANGUAGEplpython3u;
383383
SELECT test_type_unmarshal(x) FROM test_type_marshal() x;
384384
test_type_unmarshal
385385
---------------------
@@ -392,7 +392,7 @@ SELECT test_type_unmarshal(x) FROM test_type_marshal() x;
392392
CREATE DOMAIN booltrue AS bool CHECK (VALUE IS TRUE OR VALUE IS NULL);
393393
CREATE FUNCTION test_type_conversion_booltrue(x booltrue, y bool) RETURNS booltrue AS $$
394394
return y
395-
$$ LANGUAGEplpythonu;
395+
$$ LANGUAGEplpython3u;
396396
SELECT * FROM test_type_conversion_booltrue(true, true);
397397
test_type_conversion_booltrue
398398
-------------------------------
@@ -409,7 +409,7 @@ CREATE DOMAIN uint2 AS int2 CHECK (VALUE >= 0);
409409
CREATE FUNCTION test_type_conversion_uint2(x uint2, y int) RETURNS uint2 AS $$
410410
plpy.info(x, type(x))
411411
return y
412-
$$ LANGUAGEplpythonu;
412+
$$ LANGUAGEplpython3u;
413413
SELECT * FROM test_type_conversion_uint2(100::uint2, 50);
414414
INFO: (100, <class 'int'>)
415415
CONTEXT: PL/Python function "test_type_conversion_uint2"
@@ -435,7 +435,7 @@ CONTEXT: PL/Python function "test_type_conversion_uint2"
435435
CREATE DOMAIN nnint AS int CHECK (VALUE IS NOT NULL);
436436
CREATE FUNCTION test_type_conversion_nnint(x nnint, y int) RETURNS nnint AS $$
437437
return y
438-
$$ LANGUAGEplpythonu;
438+
$$ LANGUAGEplpython3u;
439439
SELECT * FROM test_type_conversion_nnint(10, 20);
440440
test_type_conversion_nnint
441441
----------------------------
@@ -452,7 +452,7 @@ CREATE DOMAIN bytea10 AS bytea CHECK (octet_length(VALUE) = 10 AND VALUE IS NOT
452452
CREATE FUNCTION test_type_conversion_bytea10(x bytea10, y bytea) RETURNS bytea10 AS $$
453453
plpy.info(x, type(x))
454454
return y
455-
$$ LANGUAGEplpythonu;
455+
$$ LANGUAGEplpython3u;
456456
SELECT * FROM test_type_conversion_bytea10('hello wold', 'hello wold');
457457
INFO: (b'hello wold', <class 'bytes'>)
458458
CONTEXT: PL/Python function "test_type_conversion_bytea10"
@@ -483,7 +483,7 @@ PL/Python function "test_type_conversion_bytea10"
483483
CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$
484484
plpy.info(x, type(x))
485485
return x
486-
$$ LANGUAGEplpythonu;
486+
$$ LANGUAGEplpython3u;
487487
SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]);
488488
INFO: ([0, 100], <class 'list'>)
489489
CONTEXT: PL/Python function "test_type_conversion_array_int4"
@@ -531,7 +531,7 @@ CONTEXT: PL/Python function "test_type_conversion_array_int4"
531531
CREATE FUNCTION test_type_conversion_array_bytea(x bytea[]) RETURNS bytea[] AS $$
532532
plpy.info(x, type(x))
533533
return x
534-
$$ LANGUAGEplpythonu;
534+
$$ LANGUAGEplpython3u;
535535
SELECT * FROM test_type_conversion_array_bytea(ARRAY[E'\\xdeadbeef'::bytea, NULL]);
536536
INFO: ([b'\xde\xad\xbe\xef', None], <class 'list'>)
537537
CONTEXT: PL/Python function "test_type_conversion_array_bytea"
@@ -542,7 +542,7 @@ CONTEXT: PL/Python function "test_type_conversion_array_bytea"
542542

543543
CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$
544544
return [123, 'abc']
545-
$$ LANGUAGEplpythonu;
545+
$$ LANGUAGEplpython3u;
546546
SELECT * FROM test_type_conversion_array_mixed1();
547547
test_type_conversion_array_mixed1
548548
-----------------------------------
@@ -551,20 +551,20 @@ SELECT * FROM test_type_conversion_array_mixed1();
551551

552552
CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$
553553
return [123, 'abc']
554-
$$ LANGUAGEplpythonu;
554+
$$ LANGUAGEplpython3u;
555555
SELECT * FROM test_type_conversion_array_mixed2();
556556
ERROR: invalid input syntax for integer: "abc"
557557
CONTEXT: while creating return value
558558
PL/Python function "test_type_conversion_array_mixed2"
559559
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
560560
return [None]
561-
$$ LANGUAGEplpythonu;
561+
$$ LANGUAGEplpython3u;
562562
SELECT * FROM test_type_conversion_array_record();
563563
ERROR: PL/Python functions cannot return type type_record[]
564564
DETAIL: PL/Python does not support conversion to arrays of row types.
565565
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
566566
return 'abc'
567-
$$ LANGUAGEplpythonu;
567+
$$ LANGUAGEplpython3u;
568568
SELECT * FROM test_type_conversion_array_string();
569569
test_type_conversion_array_string
570570
-----------------------------------
@@ -573,7 +573,7 @@ SELECT * FROM test_type_conversion_array_string();
573573

574574
CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$
575575
return ('abc', 'def')
576-
$$ LANGUAGEplpythonu;
576+
$$ LANGUAGEplpython3u;
577577
SELECT * FROM test_type_conversion_array_tuple();
578578
test_type_conversion_array_tuple
579579
----------------------------------
@@ -582,8 +582,69 @@ SELECT * FROM test_type_conversion_array_tuple();
582582

583583
CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$
584584
return 5
585-
$$ LANGUAGEplpythonu;
585+
$$ LANGUAGEplpython3u;
586586
SELECT * FROM test_type_conversion_array_error();
587587
ERROR: PL/Python: return value of function with array return type is not a Python sequence
588588
CONTEXT: while creating return value
589589
PL/Python function "test_type_conversion_array_error"
590+
--
591+
-- Prepared statements
592+
--
593+
CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int
594+
LANGUAGE plpython3u
595+
AS $$
596+
plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean'])
597+
rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python
598+
return rv[0]['val']
599+
$$;
600+
SELECT test_prep_bool_input(); -- 1
601+
test_prep_bool_input
602+
----------------------
603+
1
604+
(1 row)
605+
606+
CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool
607+
LANGUAGE plpython3u
608+
AS $$
609+
plan = plpy.prepare("SELECT $1 = 1 AS val", ['int'])
610+
rv = plpy.execute(plan, [0], 5)
611+
plpy.info(rv[0])
612+
return rv[0]['val']
613+
$$;
614+
SELECT test_prep_bool_output(); -- false
615+
INFO: {'val': False}
616+
CONTEXT: PL/Python function "test_prep_bool_output"
617+
test_prep_bool_output
618+
-----------------------
619+
f
620+
(1 row)
621+
622+
CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int
623+
LANGUAGE plpython3u
624+
AS $$
625+
plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea'])
626+
rv = plpy.execute(plan, [bb], 5)
627+
return rv[0]['val']
628+
$$;
629+
SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value)
630+
test_prep_bytea_input
631+
-----------------------
632+
3
633+
(1 row)
634+
635+
CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea
636+
LANGUAGE plpython3u
637+
AS $$
638+
plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val")
639+
rv = plpy.execute(plan, [], 5)
640+
plpy.info(rv[0])
641+
return rv[0]['val']
642+
$$;
643+
SELECT test_prep_bytea_output();
644+
INFO: {'val': b'\xaa\x00\xbb'}
645+
CONTEXT: PL/Python function "test_prep_bytea_output"
646+
test_prep_bytea_output
647+
------------------------
648+
\xaa00bb
649+
(1 row)
650+

0 commit comments

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