Hi,

Sun, 30 Apr 2006 19:14:28 +0300, Tom Lane <[EMAIL PROTECTED]>:

"Sven Suursoho" <[EMAIL PROTECTED]> writes:
Unfortunately, there is still one problem when using unpatched python,
caused by too aggressive assert.
http://mail.python.org/pipermail/python-checkins/2005-August/046571.html.

I don't think we are going to be able to accept a patch that causes the
server to crash when using any but a bleeding-edge copy of Python.

Did complete rewrite for SETOF functions: now accepts any python object for which iter(object) returns iterable object. In this way we don't have to deal with specific containers but can use unified python iterator API. It means that plpython is future-proof -- whenever python introduces new container, stored procedures already can use those without recompiling language handler.

Also integrated with regression tests and updated existing tests to use named parameters.

When using python interpreter with asserts enabled, generators still crash. But I don't think that we should drop this feature because of that. Reasons:
1) this is someone else's bug, we are using documented API correctly
2) it doesn't concern majority of users because probably there is no asserts in production packages (tested with gentoo, ubuntu, suse). This is true even for older python versions that are not patched.

And after all, we can document using sets, lists, tuples, iterators etc and explicitly state that returning generator is undefined.


--
Sven Suursoho
Index: plpython.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/plpython.c,v
retrieving revision 1.79
diff -u -r1.79 plpython.c
--- plpython.c	27 Apr 2006 14:18:07 -0000	1.79
+++ plpython.c	4 May 2006 14:05:25 -0000
@@ -19,6 +19,7 @@
 #include "catalog/pg_type.h"
 #include "commands/trigger.h"
 #include "executor/spi.h"
+#include "funcapi.h"
 #include "fmgr.h"
 #include "nodes/makefuncs.h"
 #include "parser/parse_type.h"
@@ -108,6 +109,9 @@
 	bool		fn_readonly;
 	PLyTypeInfo result;			/* also used to store info for trigger tuple
 								 * type */
+	bool	    is_setof;		/* true, if procedure returns result set */
+	PyObject    *setof;		/* contents of result set. */
+	char	    **argnames;		/* Argument names */
 	PLyTypeInfo args[FUNC_MAX_ARGS];
 	int			nargs;
 	PyObject   *code;			/* compiled procedure code */
@@ -184,6 +188,7 @@
 static HeapTuple PLy_trigger_handler(FunctionCallInfo fcinfo, PLyProcedure *);
 
 static PyObject *PLy_function_build_args(FunctionCallInfo fcinfo, PLyProcedure *);
+static void PLy_function_delete_args(PLyProcedure *);
 static PyObject *PLy_trigger_build_args(FunctionCallInfo fcinfo, PLyProcedure *,
 					   HeapTuple *);
 static HeapTuple PLy_modify_tuple(PLyProcedure *, PyObject *,
@@ -218,6 +223,7 @@
 static PyObject *PLyInt_FromString(const char *);
 static PyObject *PLyLong_FromString(const char *);
 static PyObject *PLyString_FromString(const char *);
+static HeapTuple PLyDict_ToTuple(PLyTypeInfo *, PyObject *);
 
 
 /* global data */
@@ -726,11 +732,17 @@
 
 	PG_TRY();
 	{
-		plargs = PLy_function_build_args(fcinfo, proc);
-		plrv = PLy_procedure_call(proc, "args", plargs);
-
-		Assert(plrv != NULL);
-		Assert(!PLy_error_in_progress);
+		if (!proc->is_setof || proc->setof == NULL)
+		{
+			/* python function not called yet, do it */
+			plargs = PLy_function_build_args(fcinfo, proc);
+			plrv = PLy_procedure_call(proc, "args", plargs);
+			if (!proc->is_setof)
+				/* SETOF function parameters are deleted when called last row is returned */
+				PLy_function_delete_args(proc);
+			Assert(plrv != NULL);
+			Assert(!PLy_error_in_progress);
+		}
 
 		/*
 		 * Disconnect from SPI manager and then create the return values datum
@@ -741,6 +753,67 @@
 		if (SPI_finish() != SPI_OK_FINISH)
 			elog(ERROR, "SPI_finish failed");
 
+		if (proc->is_setof)
+		{
+			bool has_error = false;
+			ReturnSetInfo *rsi = (ReturnSetInfo *)fcinfo->resultinfo;
+
+			if (proc->setof == NULL)
+			{
+				/* first time -- do checks and setup */
+				if (!rsi || !IsA(rsi, ReturnSetInfo) ||
+						(rsi->allowedModes & SFRM_ValuePerCall) == 0)
+				{
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("only value per call is allowed")));
+				}
+				rsi->returnMode = SFRM_ValuePerCall;
+
+				/* Make iterator out of returned object */
+				proc->setof = PyObject_GetIter(plrv);
+				Py_DECREF(plrv);
+				plrv = NULL;
+
+				if (proc->setof == NULL)
+					ereport(ERROR,
+							(errcode(ERRCODE_DATATYPE_MISMATCH),
+							 errmsg("returned object can not be iterated"),
+							 errdetail("SETOF must be returned as iterable object")));
+			}
+
+			/* Fetch next from iterator */
+			plrv = PyIter_Next(proc->setof);
+			if (plrv)
+				rsi->isDone = ExprMultipleResult;
+			else
+			{
+				rsi->isDone = ExprEndResult;
+				has_error = PyErr_Occurred() != NULL;
+			}
+
+			if (rsi->isDone == ExprEndResult)
+			{
+				/* Iterator is exhausted or error happened */
+				Py_DECREF(proc->setof);
+				proc->setof = NULL;
+
+				Py_XDECREF(plargs);
+				Py_XDECREF(plrv);
+				Py_XDECREF(plrv_so);
+
+				PLy_function_delete_args(proc);
+
+				if (has_error)
+					ereport(ERROR,
+							(errcode(ERRCODE_DATA_EXCEPTION),
+							 errmsg("error fetching next item from iterator")));
+
+				fcinfo->isnull = true;
+				return (Datum)NULL;
+			}
+		}
+
 		/*
 		 * If the function is declared to return void, the Python
 		 * return value must be None. For void-returning functions, we
@@ -762,10 +835,34 @@
 		else if (plrv == Py_None)
 		{
 			fcinfo->isnull = true;
-			rv = InputFunctionCall(&proc->result.out.d.typfunc,
-								   NULL,
-								   proc->result.out.d.typioparam,
-								   -1);
+			if (proc->result.is_rowtype < 1)
+				rv = InputFunctionCall(&proc->result.out.d.typfunc,
+						NULL,
+						proc->result.out.d.typioparam,
+						-1);
+			else
+				/* Tuple as None */
+				rv = (Datum) NULL;
+		}
+		else if (proc->result.is_rowtype >= 1)
+		{
+			HeapTuple   tuple;
+
+			/* returning composite type */
+			if (!PyDict_Check(plrv))
+				elog(ERROR, "tuple must be returned as dictionary");
+
+			tuple = PLyDict_ToTuple(&proc->result, plrv);
+			if (tuple != NULL)
+			{
+				fcinfo->isnull = false;
+				rv = HeapTupleGetDatum(tuple);
+			}
+			else
+			{
+				fcinfo->isnull = true;
+				rv = (Datum) NULL;
+			}
 		}
 		else
 		{
@@ -893,6 +990,8 @@
 			 * FIXME -- error check this
 			 */
 			PyList_SetItem(args, i, arg);
+			if (proc->argnames)
+				PyDict_SetItemString(proc->globals, proc->argnames[i], arg);
 			arg = NULL;
 		}
 	}
@@ -909,6 +1008,19 @@
 }
 
 
+static void
+PLy_function_delete_args(PLyProcedure *proc)
+{
+	int	i;
+
+	if (!proc->argnames)
+		return;
+
+	for (i = 0; i < proc->nargs; i++)
+		PyDict_DelItemString(proc->globals, proc->argnames[i]);
+}
+
+
 /*
  * PLyProcedure functions
  */
@@ -979,6 +1091,9 @@
 	bool		isnull;
 	int			i,
 				rv;
+	Datum		argnames;
+	Datum	    	*elems;
+	int		nelems;
 
 	procStruct = (Form_pg_proc) GETSTRUCT(procTup);
 
@@ -1010,6 +1125,9 @@
 	proc->nargs = 0;
 	proc->code = proc->statics = NULL;
 	proc->globals = proc->me = NULL;
+	proc->is_setof = procStruct->proretset;
+	proc->setof = NULL;
+	proc->argnames = NULL;
 
 	PG_TRY();
 	{
@@ -1046,9 +1164,11 @@
 			}
 
 			if (rvTypeStruct->typtype == 'c')
-				ereport(ERROR,
-						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("plpython functions cannot return tuples yet")));
+			{
+				/* Tuple: set up later, during first call to PLy_function_handler */
+				proc->result.out.d.typoid = procStruct->prorettype;
+				proc->result.is_rowtype = 2;
+			}
 			else
 				PLy_output_datum_func(&proc->result, rvTypeTup);
 
@@ -1071,6 +1191,20 @@
 		 * arguments.
 		 */
 		proc->nargs = fcinfo->nargs;
+		if (proc->nargs)
+		{
+			argnames = SysCacheGetAttr(PROCOID, procTup, Anum_pg_proc_proargnames, &isnull);
+			if (!isnull)
+			{
+				deconstruct_array(DatumGetArrayTypeP(argnames), TEXTOID, -1, false, 'i',
+						&elems, NULL, &nelems);
+				if (nelems != proc->nargs)
+					elog(ERROR,
+							"proargnames must have the same number of elements "
+							"as the function has arguments");
+				proc->argnames = (char **) PLy_malloc(sizeof(char *)*proc->nargs);
+			}
+		}
 		for (i = 0; i < fcinfo->nargs; i++)
 		{
 			HeapTuple	argTypeTup;
@@ -1099,8 +1233,11 @@
 				proc->args[i].is_rowtype = 2;	/* still need to set I/O funcs */
 
 			ReleaseSysCache(argTypeTup);
-		}
 
+			/* Fetch argument name */
+			if (proc->argnames)
+				proc->argnames[i] = PLy_strdup(DatumGetCString(DirectFunctionCall1(textout, elems[i])));
+		}
 
 		/*
 		 * get the text of the function.
@@ -1236,6 +1373,7 @@
 	if (proc->pyname)
 		PLy_free(proc->pyname);
 	for (i = 0; i < proc->nargs; i++)
+	{
 		if (proc->args[i].is_rowtype == 1)
 		{
 			if (proc->args[i].in.r.atts)
@@ -1243,6 +1381,11 @@
 			if (proc->args[i].out.r.atts)
 				PLy_free(proc->args[i].out.r.atts);
 		}
+		if (proc->argnames && proc->argnames[i])
+			PLy_free(proc->argnames[i]);
+	}
+	if (proc->argnames)
+		PLy_free(proc->argnames);
 }
 
 /* conversion functions.  remember output from python is
@@ -1501,6 +1644,78 @@
 	return dict;
 }
 
+
+static HeapTuple
+PLyDict_ToTuple(PLyTypeInfo *info, PyObject *dict)
+{
+	TupleDesc	desc;
+	HeapTuple	tuple;
+	Datum		*values;
+	char		*nulls;
+	int		i;
+
+	desc = CreateTupleDescCopy(lookup_rowtype_tupdesc(info->out.d.typoid, -1));
+
+	/* Set up tuple type, if neccessary */
+	if (info->is_rowtype == 2)
+	{
+		PLy_output_tuple_funcs(info, desc);
+		info->is_rowtype = 1;
+	}
+	Assert(info->is_rowtype == 1);
+
+	/* Build tuple */
+	values = palloc(sizeof(Datum)*desc->natts);
+	nulls = palloc(sizeof(char)*desc->natts);
+	for (i = 0;  i < desc->natts;  ++i)
+	{
+		char		*key;
+		PyObject	*value,
+				*so;
+
+		key = NameStr(desc->attrs[i]->attname);
+		value = so = NULL;
+		PG_TRY();
+		{
+			value = PyDict_GetItemString(dict, key);
+			if (value != Py_None && value != NULL)
+			{
+				char *valuestr;
+
+				so = PyObject_Str(value);
+				valuestr = PyString_AsString(so);
+				values[i] = InputFunctionCall(&info->out.r.atts[i].typfunc
+						, valuestr
+						, info->out.r.atts[i].typioparam
+						, -1);
+				Py_DECREF(so);
+				value = so = NULL;
+				nulls[i] = ' ';
+			}
+			else
+			{
+				value = NULL;
+				values[i] = (Datum) NULL;
+				nulls[i] = 'n';
+			}
+		}
+		PG_CATCH();
+		{
+			Py_XDECREF(value);
+			Py_XDECREF(so);
+			PG_RE_THROW();
+		}
+		PG_END_TRY();
+	}
+
+	tuple = heap_formtuple(desc, values, nulls);
+	FreeTupleDesc(desc);
+	pfree(values);
+	pfree(nulls);
+
+	return tuple;
+}
+
 /* initialization, some python variables function declared here */
 
 /* interface to postgresql elog */
@@ -2378,7 +2593,6 @@
 	return PLy_output(FATAL, self, args);
 }
 
-
 static PyObject *
 PLy_output(volatile int level, PyObject * self, PyObject * args)
 {
Index: expected/plpython_function.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/expected/plpython_function.out,v
retrieving revision 1.6
diff -u -r1.6 plpython_function.out
--- expected/plpython_function.out	8 Mar 2006 04:01:29 -0000	1.6
+++ expected/plpython_function.out	4 May 2006 14:05:25 -0000
@@ -55,27 +55,27 @@
 	return "failed, that wasn''t supposed to happen"
 return "succeeded, as expected"'
     LANGUAGE plpythonu;
-CREATE FUNCTION import_test_one(text) RETURNS text
+CREATE FUNCTION import_test_one(p text) RETURNS text
 	AS
 'import sha
-digest = sha.new(args[0])
+digest = sha.new(p)
 return digest.hexdigest()'
 	LANGUAGE plpythonu;
-CREATE FUNCTION import_test_two(users) RETURNS text
+CREATE FUNCTION import_test_two(u users) RETURNS text
 	AS
 'import sha
-plain = args[0]["fname"] + args[0]["lname"]
+plain = u["fname"] + u["lname"]
 digest = sha.new(plain);
 return "sha hash of " + plain + " is " + digest.hexdigest()'
 	LANGUAGE plpythonu;
-CREATE FUNCTION argument_test_one(users, text, text) RETURNS text
+CREATE FUNCTION argument_test_one(u users, a1 text, a2 text) RETURNS text
 	AS
-'keys = args[0].keys()
+'keys = u.keys()
 keys.sort()
 out = []
 for key in keys:
-    out.append("%s: %s" % (key, args[0][key]))
-words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}"
+    out.append("%s: %s" % (key, u[key]))
+words = a1 + " " + a2 + " => {" + ", ".join(out) + "}"
 return words'
 	LANGUAGE plpythonu;
 -- these triggers are dedicated to HPHC of RI who
@@ -117,40 +117,40 @@
 	EXECUTE PROCEDURE users_delete ('willem');
 -- nested calls
 --
-CREATE FUNCTION nested_call_one(text) RETURNS text
+CREATE FUNCTION nested_call_one(a text) RETURNS text
 	AS
-'q = "SELECT nested_call_two(''%s'')" % args[0]
+'q = "SELECT nested_call_two(''%s'')" % a
 r = plpy.execute(q)
 return r[0]'
 	LANGUAGE plpythonu ;
-CREATE FUNCTION nested_call_two(text) RETURNS text
+CREATE FUNCTION nested_call_two(a text) RETURNS text
 	AS
-'q = "SELECT nested_call_three(''%s'')" % args[0]
+'q = "SELECT nested_call_three(''%s'')" % a
 r = plpy.execute(q)
 return r[0]'
 	LANGUAGE plpythonu ;
-CREATE FUNCTION nested_call_three(text) RETURNS text
+CREATE FUNCTION nested_call_three(a text) RETURNS text
 	AS
-'return args[0]'
+'return a'
 	LANGUAGE plpythonu ;
 -- some spi stuff
-CREATE FUNCTION spi_prepared_plan_test_one(text) RETURNS text
+CREATE FUNCTION spi_prepared_plan_test_one(a text) RETURNS text
 	AS
 'if not SD.has_key("myplan"):
 	q = "SELECT count(*) FROM users WHERE lname = $1"
 	SD["myplan"] = plpy.prepare(q, [ "text" ])
 try:
-	rv = plpy.execute(SD["myplan"], [args[0]])
-	return "there are " + str(rv[0]["count"]) + " " + str(args[0]) + "s"
+	rv = plpy.execute(SD["myplan"], [a])
+	return "there are " + str(rv[0]["count"]) + " " + str(a) + "s"
 except Exception, ex:
 	plpy.error(str(ex))
 return None
 '
 	LANGUAGE plpythonu;
-CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text
+CREATE FUNCTION spi_prepared_plan_test_nested(a text) RETURNS text
 	AS
 'if not SD.has_key("myplan"):
-	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0]
+	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % a
 	SD["myplan"] = plpy.prepare(q)
 try:
 	rv = plpy.execute(SD["myplan"])
@@ -166,12 +166,12 @@
 CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu;
 /* a typo
 */
-CREATE FUNCTION invalid_type_uncaught(text) RETURNS text
+CREATE FUNCTION invalid_type_uncaught(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	q = "SELECT fname FROM users WHERE lname = $1"
 	SD["plan"] = plpy.prepare(q, [ "test" ])
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -180,7 +180,7 @@
 /* for what it's worth catch the exception generated by
  * the typo, and return None
  */
-CREATE FUNCTION invalid_type_caught(text) RETURNS text
+CREATE FUNCTION invalid_type_caught(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	q = "SELECT fname FROM users WHERE lname = $1"
@@ -189,7 +189,7 @@
 	except plpy.SPIError, ex:
 		plpy.notice(str(ex))
 		return None
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -198,7 +198,7 @@
 /* for what it's worth catch the exception generated by
  * the typo, and reraise it as a plain error
  */
-CREATE FUNCTION invalid_type_reraised(text) RETURNS text
+CREATE FUNCTION invalid_type_reraised(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	q = "SELECT fname FROM users WHERE lname = $1"
@@ -206,7 +206,7 @@
 		SD["plan"] = plpy.prepare(q, [ "test" ])
 	except plpy.SPIError, ex:
 		plpy.error(str(ex))
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -214,11 +214,11 @@
 	LANGUAGE plpythonu;
 /* no typo no messing about
 */
-CREATE FUNCTION valid_type(text) RETURNS text
+CREATE FUNCTION valid_type(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ])
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -243,13 +243,13 @@
 'rv = plpy.execute("SELECT test5(''foo'')")
 return rv[0]'
 	LANGUAGE plpythonu;
-CREATE FUNCTION join_sequences(sequences) RETURNS text
+CREATE FUNCTION join_sequences(s sequences) RETURNS text
 	AS
-'if not args[0]["multipart"]:
-	return args[0]["sequence"]
-q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"]
+'if not s["multipart"]:
+	return s["sequence"]
+q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % s["pid"]
 rv = plpy.execute(q)
-seq = args[0]["sequence"]
+seq = s["sequence"]
 for r in rv:
 	seq = seq + r["sequence"]
 return seq
@@ -300,3 +300,67 @@
 CREATE FUNCTION test_return_none() RETURNS int AS $$
 None
 $$ LANGUAGE plpythonu;
+--
+-- Test named parameters
+--
+CREATE FUNCTION test_param_names1(a0 integer, a1 text) RETURNS boolean AS $$
+assert a0 == args[0]
+assert a1 == args[1]
+return True
+$$ LANGUAGE plpythonu;
+CREATE FUNCTION test_param_names2(u users) RETURNS text AS $$
+assert u == args[0]
+return str(u)
+$$ LANGUAGE plpythonu;
+-- use deliberately wrong parameter names
+CREATE FUNCTION test_param_names3(a0 integer) RETURNS boolean AS $$
+try:
+	assert a1 == args[0]
+	return False
+except NameError, e:
+	assert e.args[0].find("a1") > -1
+	return True
+$$ LANGUAGE plpythonu;
+--
+-- Test returning SETOF
+--
+CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$
+return [content]*count
+$$ LANGUAGE plpythonu;
+CREATE FUNCTION test_setof_as_tuple(count integer, content text) RETURNS SETOF text AS $$
+t = ()
+for i in xrange(count):
+	t += ( content, )
+return t
+$$ LANGUAGE plpythonu;
+CREATE FUNCTION test_setof_as_iterator(count integer, content text) RETURNS SETOF text AS $$
+class producer:
+	def __init__ (self, icount, icontent):
+		self.icontent = icontent
+		self.icount = icount
+	def __iter__ (self):
+		return self
+	def next (self):
+		if self.icount == 0:
+			raise StopIteration
+		self.icount -= 1
+		return self.icontent
+return producer(count, content)
+$$ LANGUAGE plpythonu;
+--
+-- Test returning tuples
+--
+CREATE FUNCTION test_table_record(first text, second integer, retnull boolean, retempty boolean) RETURNS table_record AS $$
+if retnull:
+	return None
+if retempty:
+	return { }
+return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
+$$ LANGUAGE plpythonu;
+CREATE FUNCTION test_type_record(first text, second integer, retnull boolean, retempty boolean) RETURNS type_record AS $$
+if retnull:
+	return None
+if retempty:
+	return { }
+return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
+$$ LANGUAGE plpythonu;
Index: expected/plpython_schema.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/expected/plpython_schema.out,v
retrieving revision 1.2
diff -u -r1.2 plpython_schema.out
--- expected/plpython_schema.out	10 Jul 2005 04:56:55 -0000	1.2
+++ expected/plpython_schema.out	4 May 2006 14:05:25 -0000
@@ -44,3 +44,11 @@
 CREATE TABLE unicode_test (
 	testvalue  text NOT NULL
 );
+CREATE TABLE table_record (
+	first text,
+	second int4
+	) ;
+CREATE TYPE type_record AS (
+	first text,
+	second int4
+	) ;
Index: expected/plpython_test.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/expected/plpython_test.out,v
retrieving revision 1.4
diff -u -r1.4 plpython_test.out
--- expected/plpython_test.out	1 Mar 2006 21:09:32 -0000	1.4
+++ expected/plpython_test.out	4 May 2006 14:05:25 -0000
@@ -198,3 +198,176 @@
                   | t
 (1 row)
 
+-- Test for functions with named parameters
+SELECT test_param_names1(1,'text');
+ test_param_names1 
+-------------------
+ t
+(1 row)
+
+SELECT test_param_names2(users) from users;
+                             test_param_names2                              
+----------------------------------------------------------------------------
+ {'lname': 'doe', 'username': 'j_doe', 'userid': 1, 'fname': 'jane'}
+ {'lname': 'doe', 'username': 'johnd', 'userid': 2, 'fname': 'john'}
+ {'lname': 'doe', 'username': 'w_doe', 'userid': 3, 'fname': 'willem'}
+ {'lname': 'smith', 'username': 'slash', 'userid': 4, 'fname': 'rick'}
+ {'lname': 'smith', 'username': 'w_smith', 'userid': 5, 'fname': 'willem'}
+ {'lname': 'darwin', 'username': 'beagle', 'userid': 6, 'fname': 'charles'}
+(6 rows)
+
+SELECT test_param_names3(1);
+ test_param_names3 
+-------------------
+ t
+(1 row)
+
+-- Test set returning functions
+SELECT test_setof_as_list(0, 'list');
+ test_setof_as_list 
+--------------------
+(0 rows)
+
+SELECT test_setof_as_list(1, 'list');
+ test_setof_as_list 
+--------------------
+ list
+(1 row)
+
+SELECT test_setof_as_list(2, 'list');
+ test_setof_as_list 
+--------------------
+ list
+ list
+(2 rows)
+
+SELECT test_setof_as_list(2, null);
+ test_setof_as_list 
+--------------------
+ 
+ 
+(2 rows)
+
+SELECT test_setof_as_tuple(0, 'tuple');
+ test_setof_as_tuple 
+---------------------
+(0 rows)
+
+SELECT test_setof_as_tuple(1, 'tuple');
+ test_setof_as_tuple 
+---------------------
+ tuple
+(1 row)
+
+SELECT test_setof_as_tuple(2, 'tuple');
+ test_setof_as_tuple 
+---------------------
+ tuple
+ tuple
+(2 rows)
+
+SELECT test_setof_as_tuple(2, null);
+ test_setof_as_tuple 
+---------------------
+ 
+ 
+(2 rows)
+
+SELECT test_setof_as_iterator(0, 'list');
+ test_setof_as_iterator 
+------------------------
+(0 rows)
+
+SELECT test_setof_as_iterator(1, 'list');
+ test_setof_as_iterator 
+------------------------
+ list
+(1 row)
+
+SELECT test_setof_as_iterator(2, 'list');
+ test_setof_as_iterator 
+------------------------
+ list
+ list
+(2 rows)
+
+SELECT test_setof_as_iterator(2, null);
+ test_setof_as_iterator 
+------------------------
+ 
+ 
+(2 rows)
+
+-- Test tuple returning functions
+SELECT * FROM test_table_record(null, null, false, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_table_record('one', null, false, false);
+ first | second 
+-------+--------
+ one   |       
+(1 row)
+
+SELECT * FROM test_table_record(null, 2, false, false);
+ first | second 
+-------+--------
+       |      2
+(1 row)
+
+SELECT * FROM test_table_record('three', 3, false, false);
+ first | second 
+-------+--------
+ three |      3
+(1 row)
+
+SELECT * FROM test_table_record(null, null, true, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_table_record(null, null, false, true);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_type_record(null, null, false, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_type_record('one', null, false, false);
+ first | second 
+-------+--------
+ one   |       
+(1 row)
+
+SELECT * FROM test_type_record(null, 2, false, false);
+ first | second 
+-------+--------
+       |      2
+(1 row)
+
+SELECT * FROM test_type_record('three', 3, false, false);
+ first | second 
+-------+--------
+ three |      3
+(1 row)
+
+SELECT * FROM test_type_record(null, null, true, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_type_record(null, null, false, true);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
Index: sql/plpython_function.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/sql/plpython_function.sql,v
retrieving revision 1.6
diff -u -r1.6 plpython_function.sql
--- sql/plpython_function.sql	8 Mar 2006 04:01:29 -0000	1.6
+++ sql/plpython_function.sql	4 May 2006 14:05:25 -0000
@@ -65,29 +65,29 @@
 return "succeeded, as expected"'
     LANGUAGE plpythonu;
 
-CREATE FUNCTION import_test_one(text) RETURNS text
+CREATE FUNCTION import_test_one(p text) RETURNS text
 	AS
 'import sha
-digest = sha.new(args[0])
+digest = sha.new(p)
 return digest.hexdigest()'
 	LANGUAGE plpythonu;
 
-CREATE FUNCTION import_test_two(users) RETURNS text
+CREATE FUNCTION import_test_two(u users) RETURNS text
 	AS
 'import sha
-plain = args[0]["fname"] + args[0]["lname"]
+plain = u["fname"] + u["lname"]
 digest = sha.new(plain);
 return "sha hash of " + plain + " is " + digest.hexdigest()'
 	LANGUAGE plpythonu;
 
-CREATE FUNCTION argument_test_one(users, text, text) RETURNS text
+CREATE FUNCTION argument_test_one(u users, a1 text, a2 text) RETURNS text
 	AS
-'keys = args[0].keys()
+'keys = u.keys()
 keys.sort()
 out = []
 for key in keys:
-    out.append("%s: %s" % (key, args[0][key]))
-words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}"
+    out.append("%s: %s" % (key, u[key]))
+words = a1 + " " + a2 + " => {" + ", ".join(out) + "}"
 return words'
 	LANGUAGE plpythonu;
 
@@ -143,45 +143,45 @@
 -- nested calls
 --
 
-CREATE FUNCTION nested_call_one(text) RETURNS text
+CREATE FUNCTION nested_call_one(a text) RETURNS text
 	AS
-'q = "SELECT nested_call_two(''%s'')" % args[0]
+'q = "SELECT nested_call_two(''%s'')" % a
 r = plpy.execute(q)
 return r[0]'
 	LANGUAGE plpythonu ;
 
-CREATE FUNCTION nested_call_two(text) RETURNS text
+CREATE FUNCTION nested_call_two(a text) RETURNS text
 	AS
-'q = "SELECT nested_call_three(''%s'')" % args[0]
+'q = "SELECT nested_call_three(''%s'')" % a
 r = plpy.execute(q)
 return r[0]'
 	LANGUAGE plpythonu ;
 
-CREATE FUNCTION nested_call_three(text) RETURNS text
+CREATE FUNCTION nested_call_three(a text) RETURNS text
 	AS
-'return args[0]'
+'return a'
 	LANGUAGE plpythonu ;
 
 -- some spi stuff
 
-CREATE FUNCTION spi_prepared_plan_test_one(text) RETURNS text
+CREATE FUNCTION spi_prepared_plan_test_one(a text) RETURNS text
 	AS
 'if not SD.has_key("myplan"):
 	q = "SELECT count(*) FROM users WHERE lname = $1"
 	SD["myplan"] = plpy.prepare(q, [ "text" ])
 try:
-	rv = plpy.execute(SD["myplan"], [args[0]])
-	return "there are " + str(rv[0]["count"]) + " " + str(args[0]) + "s"
+	rv = plpy.execute(SD["myplan"], [a])
+	return "there are " + str(rv[0]["count"]) + " " + str(a) + "s"
 except Exception, ex:
 	plpy.error(str(ex))
 return None
 '
 	LANGUAGE plpythonu;
 
-CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text
+CREATE FUNCTION spi_prepared_plan_test_nested(a text) RETURNS text
 	AS
 'if not SD.has_key("myplan"):
-	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0]
+	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % a
 	SD["myplan"] = plpy.prepare(q)
 try:
 	rv = plpy.execute(SD["myplan"])
@@ -200,12 +200,12 @@
 
 /* a typo
 */
-CREATE FUNCTION invalid_type_uncaught(text) RETURNS text
+CREATE FUNCTION invalid_type_uncaught(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	q = "SELECT fname FROM users WHERE lname = $1"
 	SD["plan"] = plpy.prepare(q, [ "test" ])
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -215,7 +215,7 @@
 /* for what it's worth catch the exception generated by
  * the typo, and return None
  */
-CREATE FUNCTION invalid_type_caught(text) RETURNS text
+CREATE FUNCTION invalid_type_caught(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	q = "SELECT fname FROM users WHERE lname = $1"
@@ -224,7 +224,7 @@
 	except plpy.SPIError, ex:
 		plpy.notice(str(ex))
 		return None
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -234,7 +234,7 @@
 /* for what it's worth catch the exception generated by
  * the typo, and reraise it as a plain error
  */
-CREATE FUNCTION invalid_type_reraised(text) RETURNS text
+CREATE FUNCTION invalid_type_reraised(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	q = "SELECT fname FROM users WHERE lname = $1"
@@ -242,7 +242,7 @@
 		SD["plan"] = plpy.prepare(q, [ "test" ])
 	except plpy.SPIError, ex:
 		plpy.error(str(ex))
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -252,11 +252,11 @@
 
 /* no typo no messing about
 */
-CREATE FUNCTION valid_type(text) RETURNS text
+CREATE FUNCTION valid_type(a text) RETURNS text
 	AS
 'if not SD.has_key("plan"):
 	SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ])
-rv = plpy.execute(SD["plan"], [ args[0] ])
+rv = plpy.execute(SD["plan"], [ a ])
 if len(rv):
 	return rv[0]["fname"]
 return None
@@ -285,13 +285,13 @@
 	LANGUAGE plpythonu;
 
 
-CREATE FUNCTION join_sequences(sequences) RETURNS text
+CREATE FUNCTION join_sequences(s sequences) RETURNS text
 	AS
-'if not args[0]["multipart"]:
-	return args[0]["sequence"]
-q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"]
+'if not s["multipart"]:
+	return s["sequence"]
+q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % s["pid"]
 rv = plpy.execute(q)
-seq = args[0]["sequence"]
+seq = s["sequence"]
 for r in rv:
 	seq = seq + r["sequence"]
 return seq
@@ -356,3 +356,78 @@
 CREATE FUNCTION test_return_none() RETURNS int AS $$
 None
 $$ LANGUAGE plpythonu;
+
+
+--
+-- Test named parameters
+--
+CREATE FUNCTION test_param_names1(a0 integer, a1 text) RETURNS boolean AS $$
+assert a0 == args[0]
+assert a1 == args[1]
+return True
+$$ LANGUAGE plpythonu;
+
+CREATE FUNCTION test_param_names2(u users) RETURNS text AS $$
+assert u == args[0]
+return str(u)
+$$ LANGUAGE plpythonu;
+
+-- use deliberately wrong parameter names
+CREATE FUNCTION test_param_names3(a0 integer) RETURNS boolean AS $$
+try:
+	assert a1 == args[0]
+	return False
+except NameError, e:
+	assert e.args[0].find("a1") > -1
+	return True
+$$ LANGUAGE plpythonu;
+
+
+--
+-- Test returning SETOF
+--
+CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$
+return [content]*count
+$$ LANGUAGE plpythonu;
+
+CREATE FUNCTION test_setof_as_tuple(count integer, content text) RETURNS SETOF text AS $$
+t = ()
+for i in xrange(count):
+	t += ( content, )
+return t
+$$ LANGUAGE plpythonu;
+
+CREATE FUNCTION test_setof_as_iterator(count integer, content text) RETURNS SETOF text AS $$
+class producer:
+	def __init__ (self, icount, icontent):
+		self.icontent = icontent
+		self.icount = icount
+	def __iter__ (self):
+		return self
+	def next (self):
+		if self.icount == 0:
+			raise StopIteration
+		self.icount -= 1
+		return self.icontent
+return producer(count, content)
+$$ LANGUAGE plpythonu;
+
+
+--
+-- Test returning tuples
+--
+CREATE FUNCTION test_table_record(first text, second integer, retnull boolean, retempty boolean) RETURNS table_record AS $$
+if retnull:
+	return None
+if retempty:
+	return { }
+return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
+$$ LANGUAGE plpythonu;
+
+CREATE FUNCTION test_type_record(first text, second integer, retnull boolean, retempty boolean) RETURNS type_record AS $$
+if retnull:
+	return None
+if retempty:
+	return { }
+return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
+$$ LANGUAGE plpythonu;
Index: sql/plpython_populate.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/sql/plpython_populate.sql,v
retrieving revision 1.1
diff -u -r1.1 plpython_populate.sql
--- sql/plpython_populate.sql	14 May 2005 17:55:21 -0000	1.1
+++ sql/plpython_populate.sql	4 May 2006 14:05:25 -0000
@@ -25,4 +25,4 @@
 INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 3, 'env') ;
 INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 4, 'gag') ;
 INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 5, 'env') ;
-INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ;
\ No newline at end of file
+INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ;
Index: sql/plpython_schema.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/sql/plpython_schema.sql,v
retrieving revision 1.2
diff -u -r1.2 plpython_schema.sql
--- sql/plpython_schema.sql	10 Jul 2005 04:56:55 -0000	1.2
+++ sql/plpython_schema.sql	4 May 2006 14:05:25 -0000
@@ -42,3 +42,13 @@
 CREATE TABLE unicode_test (
 	testvalue  text NOT NULL
 );
+
+CREATE TABLE table_record (
+	first text,
+	second int4
+	) ;
+
+CREATE TYPE type_record AS (
+	first text,
+	second int4
+	) ;
Index: sql/plpython_test.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/sql/plpython_test.sql,v
retrieving revision 1.2
diff -u -r1.2 plpython_test.sql
--- sql/plpython_test.sql	28 Feb 2006 20:03:52 -0000	1.2
+++ sql/plpython_test.sql	4 May 2006 14:05:26 -0000
@@ -73,3 +73,36 @@
 SELECT test_void_func1(), test_void_func1() IS NULL AS "is null";
 SELECT test_void_func2(); -- should fail
 SELECT test_return_none(), test_return_none() IS NULL AS "is null";
+
+-- Test for functions with named parameters
+SELECT test_param_names1(1,'text');
+SELECT test_param_names2(users) from users;
+SELECT test_param_names3(1);
+
+-- Test set returning functions
+SELECT test_setof_as_list(0, 'list');
+SELECT test_setof_as_list(1, 'list');
+SELECT test_setof_as_list(2, 'list');
+SELECT test_setof_as_list(2, null);
+SELECT test_setof_as_tuple(0, 'tuple');
+SELECT test_setof_as_tuple(1, 'tuple');
+SELECT test_setof_as_tuple(2, 'tuple');
+SELECT test_setof_as_tuple(2, null);
+SELECT test_setof_as_iterator(0, 'list');
+SELECT test_setof_as_iterator(1, 'list');
+SELECT test_setof_as_iterator(2, 'list');
+SELECT test_setof_as_iterator(2, null);
+
+-- Test tuple returning functions
+SELECT * FROM test_table_record(null, null, false, false);
+SELECT * FROM test_table_record('one', null, false, false);
+SELECT * FROM test_table_record(null, 2, false, false);
+SELECT * FROM test_table_record('three', 3, false, false);
+SELECT * FROM test_table_record(null, null, true, false);
+SELECT * FROM test_table_record(null, null, false, true);
+SELECT * FROM test_type_record(null, null, false, false);
+SELECT * FROM test_type_record('one', null, false, false);
+SELECT * FROM test_type_record(null, 2, false, false);
+SELECT * FROM test_type_record('three', 3, false, false);
+SELECT * FROM test_type_record(null, null, true, false);
+SELECT * FROM test_type_record(null, null, false, true);
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to