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

Commitdb73861

Browse files
committed
PL/Python array support
Support arrays as parameters and return values of PL/Python functions.
1 parenta37b001 commitdb73861

File tree

4 files changed

+343
-4
lines changed

4 files changed

+343
-4
lines changed

‎doc/src/sgml/plpython.sgml

Lines changed: 38 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.40 2009/03/30 16:15:43 alvherre Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.41 2009/12/10 20:43:40 petere Exp $ -->
22

33
<chapter id="plpython">
44
<title>PL/Python - Python Procedural Language</title>
@@ -134,6 +134,43 @@ $$ LANGUAGE plpythonu;
134134
function is strict or not.
135135
</para>
136136

137+
<para>
138+
SQL array values are passed into PL/Python as a Python list. To
139+
return an SQL array value out of a PL/Python function, return a
140+
Python sequence, for example a list or tuple:
141+
142+
<programlisting>
143+
CREATE FUNCTION return_arr()
144+
RETURNS int[]
145+
AS $$
146+
return (1, 2, 3, 4, 5)
147+
$$ LANGUAGE plpythonu;
148+
149+
SELECT return_arr();
150+
return_arr
151+
-------------
152+
{1,2,3,4,5}
153+
(1 row)
154+
</programlisting>
155+
156+
Note that in Python, strings are sequences, which can have
157+
undesirable effects that might be familiar to Python programmers:
158+
159+
<programlisting>
160+
CREATE FUNCTION return_str_arr()
161+
RETURNS varchar[]
162+
AS $$
163+
return "hello"
164+
$$ LANGUAGE plpythonu;
165+
166+
SELECT return_str_arr();
167+
return_str_arr
168+
----------------
169+
{h,e,l,l,o}
170+
(1 row)
171+
</programlisting>
172+
</para>
173+
137174
<para>
138175
Composite-type arguments are passed to the function as Python mappings. The
139176
element names of the mapping are the attribute names of the composite type.

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

Lines changed: 110 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -477,3 +477,113 @@ CONTEXT: PL/Python function "test_type_conversion_bytea10"
477477
ERROR: value for domain bytea10 violates check constraint "bytea10_check"
478478
CONTEXT: while creating return value
479479
PL/Python function "test_type_conversion_bytea10"
480+
--
481+
-- Arrays
482+
--
483+
CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$
484+
plpy.info(x, type(x))
485+
return x
486+
$$ LANGUAGE plpythonu;
487+
SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]);
488+
INFO: ([0, 100], <type 'list'>)
489+
CONTEXT: PL/Python function "test_type_conversion_array_int4"
490+
test_type_conversion_array_int4
491+
---------------------------------
492+
{0,100}
493+
(1 row)
494+
495+
SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]);
496+
INFO: ([0, -100, 55], <type 'list'>)
497+
CONTEXT: PL/Python function "test_type_conversion_array_int4"
498+
test_type_conversion_array_int4
499+
---------------------------------
500+
{0,-100,55}
501+
(1 row)
502+
503+
SELECT * FROM test_type_conversion_array_int4(ARRAY[NULL,1]);
504+
INFO: ([None, 1], <type 'list'>)
505+
CONTEXT: PL/Python function "test_type_conversion_array_int4"
506+
test_type_conversion_array_int4
507+
---------------------------------
508+
{NULL,1}
509+
(1 row)
510+
511+
SELECT * FROM test_type_conversion_array_int4(ARRAY[]::integer[]);
512+
INFO: ([], <type 'list'>)
513+
CONTEXT: PL/Python function "test_type_conversion_array_int4"
514+
test_type_conversion_array_int4
515+
---------------------------------
516+
{}
517+
(1 row)
518+
519+
SELECT * FROM test_type_conversion_array_int4(NULL);
520+
INFO: (None, <type 'NoneType'>)
521+
CONTEXT: PL/Python function "test_type_conversion_array_int4"
522+
test_type_conversion_array_int4
523+
---------------------------------
524+
525+
(1 row)
526+
527+
SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
528+
ERROR: cannot convert multidimensional array to Python list
529+
DETAIL: PL/Python only supports one-dimensional arrays.
530+
CONTEXT: PL/Python function "test_type_conversion_array_int4"
531+
CREATE FUNCTION test_type_conversion_array_bytea(x bytea[]) RETURNS bytea[] AS $$
532+
plpy.info(x, type(x))
533+
return x
534+
$$ LANGUAGE plpythonu;
535+
SELECT * FROM test_type_conversion_array_bytea(ARRAY[E'\\xdeadbeef'::bytea, NULL]);
536+
INFO: (['\xde\xad\xbe\xef', None], <type 'list'>)
537+
CONTEXT: PL/Python function "test_type_conversion_array_bytea"
538+
test_type_conversion_array_bytea
539+
----------------------------------
540+
{"\\xdeadbeef",NULL}
541+
(1 row)
542+
543+
CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$
544+
return [123, 'abc']
545+
$$ LANGUAGE plpythonu;
546+
SELECT * FROM test_type_conversion_array_mixed1();
547+
test_type_conversion_array_mixed1
548+
-----------------------------------
549+
{123,abc}
550+
(1 row)
551+
552+
CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$
553+
return [123, 'abc']
554+
$$ LANGUAGE plpythonu;
555+
SELECT * FROM test_type_conversion_array_mixed2();
556+
ERROR: invalid input syntax for integer: "abc"
557+
CONTEXT: while creating return value
558+
PL/Python function "test_type_conversion_array_mixed2"
559+
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
560+
return [None]
561+
$$ LANGUAGE plpythonu;
562+
SELECT * FROM test_type_conversion_array_record();
563+
ERROR: PL/Python functions cannot return type type_record[]
564+
DETAIL: PL/Python does not support conversion to arrays of row types.
565+
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
566+
return 'abc'
567+
$$ LANGUAGE plpythonu;
568+
SELECT * FROM test_type_conversion_array_string();
569+
test_type_conversion_array_string
570+
-----------------------------------
571+
{a,b,c}
572+
(1 row)
573+
574+
CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$
575+
return ('abc', 'def')
576+
$$ LANGUAGE plpythonu;
577+
SELECT * FROM test_type_conversion_array_tuple();
578+
test_type_conversion_array_tuple
579+
----------------------------------
580+
{abc,def}
581+
(1 row)
582+
583+
CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$
584+
return 5
585+
$$ LANGUAGE plpythonu;
586+
SELECT * FROM test_type_conversion_array_error();
587+
ERROR: PL/Python: return value of function with array return type is not a Python sequence
588+
CONTEXT: while creating return value
589+
PL/Python function "test_type_conversion_array_error"

‎src/pl/plpython/plpython.c

Lines changed: 130 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/**********************************************************************
22
* plpython.c - python as a procedural language for PostgreSQL
33
*
4-
*$PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.132 2009/11/03 11:05:02 petere Exp $
4+
*$PostgreSQL: pgsql/src/pl/plpython/plpython.c,v 1.133 2009/12/10 20:43:40 petere Exp $
55
*
66
*********************************************************************
77
*/
@@ -89,6 +89,9 @@ typedef struct PLyDatumToOb
8989
Oidtypoid;/* The OID of the type */
9090
Oidtypioparam;
9191
booltypbyval;
92+
int16typlen;
93+
chartypalign;
94+
structPLyDatumToOb*elm;
9295
}PLyDatumToOb;
9396

9497
typedefstructPLyTupleToOb
@@ -120,6 +123,9 @@ typedef struct PLyObToDatum
120123
Oidtypoid;/* The OID of the type */
121124
Oidtypioparam;
122125
booltypbyval;
126+
int16typlen;
127+
chartypalign;
128+
structPLyObToDatum*elm;
123129
}PLyObToDatum;
124130

125131
typedefstructPLyObToTuple
@@ -284,6 +290,7 @@ static PyObject *PLyInt_FromInt32(PLyDatumToOb *arg, Datum d);
284290
staticPyObject*PLyLong_FromInt64(PLyDatumToOb*arg,Datumd);
285291
staticPyObject*PLyString_FromBytea(PLyDatumToOb*arg,Datumd);
286292
staticPyObject*PLyString_FromDatum(PLyDatumToOb*arg,Datumd);
293+
staticPyObject*PLyList_FromArray(PLyDatumToOb*arg,Datumd);
287294

288295
staticPyObject*PLyDict_FromTuple(PLyTypeInfo*,HeapTuple,TupleDesc);
289296

@@ -293,6 +300,8 @@ static Datum PLyObject_ToBytea(PLyTypeInfo *, PLyObToDatum *,
293300
PyObject*);
294301
staticDatumPLyObject_ToDatum(PLyTypeInfo*,PLyObToDatum*,
295302
PyObject*);
303+
staticDatumPLySequence_ToArray(PLyTypeInfo*,PLyObToDatum*,
304+
PyObject*);
296305

297306
staticHeapTuplePLyMapping_ToTuple(PLyTypeInfo*,PyObject*);
298307
staticHeapTuplePLySequence_ToTuple(PLyTypeInfo*,PyObject*);
@@ -1653,18 +1662,21 @@ static void
16531662
PLy_output_datum_func2(PLyObToDatum*arg,HeapTupletypeTup)
16541663
{
16551664
Form_pg_typetypeStruct= (Form_pg_type)GETSTRUCT(typeTup);
1665+
Oidelement_type;
16561666

16571667
perm_fmgr_info(typeStruct->typinput,&arg->typfunc);
16581668
arg->typoid=HeapTupleGetOid(typeTup);
16591669
arg->typioparam=getTypeIOParam(typeTup);
16601670
arg->typbyval=typeStruct->typbyval;
16611671

1672+
element_type=get_element_type(arg->typoid);
1673+
16621674
/*
16631675
* Select a conversion function to convert Python objects to
16641676
* PostgreSQL datums. Most data types can go through the generic
16651677
* function.
16661678
*/
1667-
switch (getBaseType(arg->typoid))
1679+
switch (getBaseType(element_type ?element_type :arg->typoid))
16681680
{
16691681
caseBOOLOID:
16701682
arg->func=PLyObject_ToBool;
@@ -1676,6 +1688,29 @@ PLy_output_datum_func2(PLyObToDatum *arg, HeapTuple typeTup)
16761688
arg->func=PLyObject_ToDatum;
16771689
break;
16781690
}
1691+
1692+
if (element_type)
1693+
{
1694+
chardummy_delim;
1695+
Oidfuncid;
1696+
1697+
if (type_is_rowtype(element_type))
1698+
ereport(ERROR,
1699+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1700+
errmsg("PL/Python functions cannot return type %s",
1701+
format_type_be(arg->typoid)),
1702+
errdetail("PL/Python does not support conversion to arrays of row types.")));
1703+
1704+
arg->elm=PLy_malloc0(sizeof(*arg->elm));
1705+
arg->elm->func=arg->func;
1706+
arg->func=PLySequence_ToArray;
1707+
1708+
arg->elm->typoid=element_type;
1709+
get_type_io_data(element_type,IOFunc_input,
1710+
&arg->elm->typlen,&arg->elm->typbyval,&arg->elm->typalign,&dummy_delim,
1711+
&arg->elm->typioparam,&funcid);
1712+
perm_fmgr_info(funcid,&arg->elm->typfunc);
1713+
}
16791714
}
16801715

16811716
staticvoid
@@ -1691,15 +1726,17 @@ static void
16911726
PLy_input_datum_func2(PLyDatumToOb*arg,OidtypeOid,HeapTupletypeTup)
16921727
{
16931728
Form_pg_typetypeStruct= (Form_pg_type)GETSTRUCT(typeTup);
1729+
Oidelement_type=get_element_type(typeOid);
16941730

16951731
/* Get the type's conversion information */
16961732
perm_fmgr_info(typeStruct->typoutput,&arg->typfunc);
16971733
arg->typoid=HeapTupleGetOid(typeTup);
16981734
arg->typioparam=getTypeIOParam(typeTup);
16991735
arg->typbyval=typeStruct->typbyval;
1736+
arg->typlen=typeStruct->typlen;
17001737

17011738
/* Determine which kind of Python object we will convert to */
1702-
switch (getBaseType(typeOid))
1739+
switch (getBaseType(element_type ?element_type :typeOid))
17031740
{
17041741
caseBOOLOID:
17051742
arg->func=PLyBool_FromBool;
@@ -1729,6 +1766,14 @@ PLy_input_datum_func2(PLyDatumToOb *arg, Oid typeOid, HeapTuple typeTup)
17291766
arg->func=PLyString_FromDatum;
17301767
break;
17311768
}
1769+
1770+
if (element_type)
1771+
{
1772+
arg->elm=PLy_malloc0(sizeof(*arg->elm));
1773+
arg->elm->func=arg->func;
1774+
arg->func=PLyList_FromArray;
1775+
get_typlenbyvalalign(element_type,&arg->elm->typlen,&arg->elm->typbyval,&arg->elm->typalign);
1776+
}
17321777
}
17331778

17341779
staticvoid
@@ -1832,6 +1877,45 @@ PLyString_FromDatum(PLyDatumToOb *arg, Datum d)
18321877
returnr;
18331878
}
18341879

1880+
staticPyObject*
1881+
PLyList_FromArray(PLyDatumToOb*arg,Datumd)
1882+
{
1883+
ArrayType*array=DatumGetArrayTypeP(d);
1884+
PyObject*list;
1885+
intlength;
1886+
intlbound;
1887+
inti;
1888+
1889+
if (ARR_NDIM(array)==0)
1890+
returnPyList_New(0);
1891+
1892+
if (ARR_NDIM(array)!=1)
1893+
ereport(ERROR,
1894+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1895+
errmsg("cannot convert multidimensional array to Python list"),
1896+
errdetail("PL/Python only supports one-dimensional arrays.")));
1897+
1898+
length=ARR_DIMS(array)[0];
1899+
lbound=ARR_LBOUND(array)[0];
1900+
list=PyList_New(length);
1901+
1902+
for (i=0;i<length;i++)
1903+
{
1904+
Datumelem;
1905+
boolisnull;
1906+
intoffset;
1907+
1908+
offset=lbound+i;
1909+
elem=array_ref(array,1,&offset,arg->typlen,arg->elm->typlen,arg->elm->typbyval,arg->elm->typalign,&isnull);
1910+
if (isnull)
1911+
PyList_SET_ITEM(list,i,Py_None);
1912+
else
1913+
PyList_SET_ITEM(list,i,arg->elm->func(arg,elem));
1914+
}
1915+
1916+
returnlist;
1917+
}
1918+
18351919
staticPyObject*
18361920
PLyDict_FromTuple(PLyTypeInfo*info,HeapTupletuple,TupleDescdesc)
18371921
{
@@ -1994,6 +2078,49 @@ PLyObject_ToDatum(PLyTypeInfo *info,
19942078
returnrv;
19952079
}
19962080

2081+
staticDatum
2082+
PLySequence_ToArray(PLyTypeInfo*info,
2083+
PLyObToDatum*arg,
2084+
PyObject*plrv)
2085+
{
2086+
ArrayType*array;
2087+
inti;
2088+
Datum*elems;
2089+
bool*nulls;
2090+
intlen;
2091+
intlbs;
2092+
2093+
Assert(plrv!=Py_None);
2094+
2095+
if (!PySequence_Check(plrv))
2096+
PLy_elog(ERROR,"return value of function with array return type is not a Python sequence");
2097+
2098+
len=PySequence_Length(plrv);
2099+
elems=palloc(sizeof(*elems)*len);
2100+
nulls=palloc(sizeof(*nulls)*len);
2101+
2102+
for (i=0;i<len;i++)
2103+
{
2104+
PyObject*obj=PySequence_GetItem(plrv,i);
2105+
2106+
if (obj==Py_None)
2107+
nulls[i]= true;
2108+
else
2109+
{
2110+
nulls[i]= false;
2111+
/* We don't support arrays of row types yet, so the first
2112+
* argument can be NULL. */
2113+
elems[i]=arg->elm->func(NULL,arg->elm,obj);
2114+
}
2115+
Py_XDECREF(obj);
2116+
}
2117+
2118+
lbs=1;
2119+
array=construct_md_array(elems,nulls,1,&len,&lbs,
2120+
get_element_type(arg->typoid),arg->elm->typlen,arg->elm->typbyval,arg->elm->typalign);
2121+
returnPointerGetDatum(array);
2122+
}
2123+
19972124
staticHeapTuple
19982125
PLyMapping_ToTuple(PLyTypeInfo*info,PyObject*mapping)
19992126
{

0 commit comments

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