Hi,

Thu, 27 Apr 2006 17:17:36 +0300, Bruce Momjian <pgman@candle.pha.pa.us>:

Sorry, I have to revert this patch because it is causing crashes in the
plpython regression tests.  Would you please run those tests, fix the
bug, and resubmit.  Thanks.

Found and fixed two problems:
1) named parameters handling if there were no parameters at all
2) didn't increment counter for borrowed reference fetched from list

Also integrated tests to plpython test suite and updated existing tests to use named parameters.

Unfortunately, there is still one problem when using unpatched python, caused by too aggressive assert. See http://mail.python.org/pipermail/python-checkins/2005-August/046571.html. I guess there should be warning somewhere as Hannu said but didn't know where to put it.


--
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	29 Apr 2006 10:52:22 -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,11 @@
 	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. */
+	int	    setof_count;	/* numbef of items to return in result set */
+	int	    setof_current;	/* current item in result set */
+	char	    **argnames;		/* Argument names */
 	PLyTypeInfo args[FUNC_MAX_ARGS];
 	int			nargs;
 	PyObject   *code;			/* compiled procedure code */
@@ -184,6 +190,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 +225,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 +734,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_count == -1)
+		{
+			/* 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 +755,82 @@
 		if (SPI_finish() != SPI_OK_FINISH)
 			elog(ERROR, "SPI_finish failed");
 
+		if (proc->is_setof)
+		{
+			bool is_done = false;
+			ReturnSetInfo *rsi = (ReturnSetInfo *)fcinfo->resultinfo;
+
+			if (proc->setof_current == -1)
+			{
+				/* 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;
+
+				/* fetch information about returned object */
+				proc->setof = plrv;
+				plrv = NULL;
+				if (PyList_Check(proc->setof))
+					/* SETOF as list */
+					proc->setof_count = PyList_GET_SIZE(proc->setof);
+				else if (PyIter_Check(proc->setof))
+					/* SETOF as iterator, unknown number of items */
+					proc->setof_current = proc->setof_count = 0;
+				else
+				{
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("SETOF must be returned as list, iterator or generator")));
+				}
+			}
+
+			Assert(proc->setof != NULL);
+
+			/* Fetch next of SETOF */
+			if (PyList_Check(proc->setof))
+			{
+				is_done = ++proc->setof_current == proc->setof_count;
+				if (!is_done)
+				{
+					plrv = PyList_GET_ITEM(proc->setof, proc->setof_current);
+
+					/* Got 'borrowed reference', must increment reference count
+					 * to balance later decrement */
+					Py_INCREF(plrv);
+				}
+			}
+			else if (PyIter_Check(proc->setof))
+			{
+				plrv = PyIter_Next(proc->setof);
+				is_done = plrv == NULL;
+			}
+
+			if (!is_done)
+			{
+				rsi->isDone = ExprMultipleResult;
+			}
+			else
+			{
+				rsi->isDone = ExprEndResult;
+				proc->setof_count = proc->setof_current = -1;
+				Py_DECREF(proc->setof);
+				proc->setof = NULL;
+
+				Py_XDECREF(plargs);
+				Py_XDECREF(plrv);
+				Py_XDECREF(plrv_so);
+
+				PLy_function_delete_args(proc);
+				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 +852,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 +1007,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 +1025,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 +1108,9 @@
 	bool		isnull;
 	int			i,
 				rv;
+	Datum		argnames;
+	Datum	    	*elems;
+	int		nelems;
 
 	procStruct = (Form_pg_proc) GETSTRUCT(procTup);
 
@@ -1010,6 +1142,10 @@
 	proc->nargs = 0;
 	proc->code = proc->statics = NULL;
 	proc->globals = proc->me = NULL;
+	proc->is_setof = procStruct->proretset;
+	proc->setof = NULL;
+	proc->setof_count = proc->setof_current = -1;
+	proc->argnames = NULL;
 
 	PG_TRY();
 	{
@@ -1046,9 +1182,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 +1209,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 +1251,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 +1391,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 +1399,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 +1662,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 */
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	29 Apr 2006 10:52:22 -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,65 @@
 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' is not defined") > -1
+	return True
+$$ LANGUAGE plpythonu;
+--
+-- Test returning SETOF
+--
+CREATE FUNCTION test_setof_list(count integer, content text) RETURNS SETOF text AS $$
+return [content]*count
+$$ LANGUAGE plpythonu;
+CREATE FUNCTION test_setof_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;
+CREATE FUNCTION test_setof_generator(count integer, content text) RETURNS SETOF text AS $$
+for i in range(count):
+	yield content
+$$ LANGUAGE plpythonu;
+--
+-- Test returning tuples
+--
+CREATE FUNCTION test_table_tuple(first text, second integer, retnull boolean, retempty boolean) RETURNS table_tuple AS $$
+if retnull:
+	return None
+if retempty:
+	return { }
+return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
+$$ LANGUAGE plpythonu;
+CREATE FUNCTION test_type_tuple(first text, second integer, retnull boolean, retempty boolean) RETURNS type_tuple 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	29 Apr 2006 10:52:22 -0000
@@ -44,3 +44,11 @@
 CREATE TABLE unicode_test (
 	testvalue  text NOT NULL
 );
+CREATE TABLE table_tuple (
+	first text,
+	second int4
+	) ;
+CREATE TYPE type_tuple 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	29 Apr 2006 10:52:22 -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_list(0, 'list');
+ test_setof_list 
+-----------------
+(0 rows)
+
+SELECT test_setof_list(1, 'list');
+ test_setof_list 
+-----------------
+ list
+(1 row)
+
+SELECT test_setof_list(2, 'list');
+ test_setof_list 
+-----------------
+ list
+ list
+(2 rows)
+
+SELECT test_setof_list(2, null);
+ test_setof_list 
+-----------------
+ 
+ 
+(2 rows)
+
+SELECT test_setof_iterator(0, 'list');
+ test_setof_iterator 
+---------------------
+(0 rows)
+
+SELECT test_setof_iterator(1, 'list');
+ test_setof_iterator 
+---------------------
+ list
+(1 row)
+
+SELECT test_setof_iterator(2, 'list');
+ test_setof_iterator 
+---------------------
+ list
+ list
+(2 rows)
+
+SELECT test_setof_iterator(2, null);
+ test_setof_iterator 
+---------------------
+ 
+ 
+(2 rows)
+
+SELECT test_setof_generator(0, 'generator');
+ test_setof_generator 
+----------------------
+(0 rows)
+
+SELECT test_setof_generator(1, 'generator');
+ test_setof_generator 
+----------------------
+ generator
+(1 row)
+
+SELECT test_setof_generator(2, 'generator');
+ test_setof_generator 
+----------------------
+ generator
+ generator
+(2 rows)
+
+SELECT test_setof_generator(2, null);
+ test_setof_generator 
+----------------------
+ 
+ 
+(2 rows)
+
+-- Test tuple returning functions
+SELECT * FROM test_table_tuple(null, null, false, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_table_tuple('one', null, false, false);
+ first | second 
+-------+--------
+ one   |       
+(1 row)
+
+SELECT * FROM test_table_tuple(null, 2, false, false);
+ first | second 
+-------+--------
+       |      2
+(1 row)
+
+SELECT * FROM test_table_tuple('three', 3, false, false);
+ first | second 
+-------+--------
+ three |      3
+(1 row)
+
+SELECT * FROM test_table_tuple(null, null, true, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_table_tuple(null, null, false, true);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_type_tuple(null, null, false, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_type_tuple('one', null, false, false);
+ first | second 
+-------+--------
+ one   |       
+(1 row)
+
+SELECT * FROM test_type_tuple(null, 2, false, false);
+ first | second 
+-------+--------
+       |      2
+(1 row)
+
+SELECT * FROM test_type_tuple('three', 3, false, false);
+ first | second 
+-------+--------
+ three |      3
+(1 row)
+
+SELECT * FROM test_type_tuple(null, null, true, false);
+ first | second 
+-------+--------
+       |       
+(1 row)
+
+SELECT * FROM test_type_tuple(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	29 Apr 2006 10:52:22 -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,76 @@
 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' is not defined") > -1
+	return True
+$$ LANGUAGE plpythonu;
+
+
+--
+-- Test returning SETOF
+--
+CREATE FUNCTION test_setof_list(count integer, content text) RETURNS SETOF text AS $$
+return [content]*count
+$$ LANGUAGE plpythonu;
+
+CREATE FUNCTION test_setof_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;
+
+CREATE FUNCTION test_setof_generator(count integer, content text) RETURNS SETOF text AS $$
+for i in range(count):
+	yield content
+$$ LANGUAGE plpythonu;
+
+
+--
+-- Test returning tuples
+--
+CREATE FUNCTION test_table_tuple(first text, second integer, retnull boolean, retempty boolean) RETURNS table_tuple AS $$
+if retnull:
+	return None
+if retempty:
+	return { }
+return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
+$$ LANGUAGE plpythonu;
+
+CREATE FUNCTION test_type_tuple(first text, second integer, retnull boolean, retempty boolean) RETURNS type_tuple 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	29 Apr 2006 10:52:22 -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	29 Apr 2006 10:52:22 -0000
@@ -42,3 +42,13 @@
 CREATE TABLE unicode_test (
 	testvalue  text NOT NULL
 );
+
+CREATE TABLE table_tuple (
+	first text,
+	second int4
+	) ;
+
+CREATE TYPE type_tuple 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	29 Apr 2006 10:52:22 -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_list(0, 'list');
+SELECT test_setof_list(1, 'list');
+SELECT test_setof_list(2, 'list');
+SELECT test_setof_list(2, null);
+SELECT test_setof_iterator(0, 'list');
+SELECT test_setof_iterator(1, 'list');
+SELECT test_setof_iterator(2, 'list');
+SELECT test_setof_iterator(2, null);
+SELECT test_setof_generator(0, 'generator');
+SELECT test_setof_generator(1, 'generator');
+SELECT test_setof_generator(2, 'generator');
+SELECT test_setof_generator(2, null);
+
+-- Test tuple returning functions
+SELECT * FROM test_table_tuple(null, null, false, false);
+SELECT * FROM test_table_tuple('one', null, false, false);
+SELECT * FROM test_table_tuple(null, 2, false, false);
+SELECT * FROM test_table_tuple('three', 3, false, false);
+SELECT * FROM test_table_tuple(null, null, true, false);
+SELECT * FROM test_table_tuple(null, null, false, true);
+SELECT * FROM test_type_tuple(null, null, false, false);
+SELECT * FROM test_type_tuple('one', null, false, false);
+SELECT * FROM test_type_tuple(null, 2, false, false);
+SELECT * FROM test_type_tuple('three', 3, false, false);
+SELECT * FROM test_type_tuple(null, null, true, false);
+SELECT * FROM test_type_tuple(null, null, false, true);
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to