On Sat, 17 Jun 2006 18:47:46 +0300, Tom Lane <[EMAIL PROTECTED]> wrote:

Obviously we should document the problem in the plpython documentation
("don't try to use this feature with python versions before XXX"), but
I'm not any longer convinced that we have to reject this patch on
security grounds.

Here it is, updated against CVS HEAD.
Will create documentation after acceptance of patch.


--
Sven Suursoho
Index: plpython.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/plpython.c,v
retrieving revision 1.82
diff -c -r1.82 plpython.c
*** plpython.c	16 Jun 2006 18:42:23 -0000	1.82
--- plpython.c	18 Jun 2006 09:14:20 -0000
***************
*** 19,24 ****
--- 19,25 ----
  #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"
***************
*** 110,115 ****
--- 111,119 ----
  	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 */
***************
*** 186,191 ****
--- 190,196 ----
  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 *,
***************
*** 221,226 ****
--- 226,235 ----
  static PyObject *PLyLong_FromString(const char *);
  static PyObject *PLyString_FromString(const char *);
  
+ static HeapTuple PLyMapping_ToTuple(PLyTypeInfo *, PyObject *);
+ static HeapTuple PLySequence_ToTuple(PLyTypeInfo *, PyObject *);
+ static HeapTuple PLyObject_ToTuple(PLyTypeInfo *, PyObject *);
+ 
  
  /* global data */
  static bool	PLy_first_call = true;
***************
*** 743,753 ****
  
  	PG_TRY();
  	{
! 		plargs = PLy_function_build_args(fcinfo, proc);
! 		plrv = PLy_procedure_call(proc, "args", plargs);
! 
! 		Assert(plrv != NULL);
! 		Assert(!PLy_error_in_progress);
  
  		/*
  		 * Disconnect from SPI manager and then create the return values datum
--- 752,768 ----
  
  	PG_TRY();
  	{
! 		if (!proc->is_setof || proc->setof == NULL)
! 		{
! 			/* Simple type returning function or first time for SETOF function */
! 			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
***************
*** 758,763 ****
--- 773,839 ----
  		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
***************
*** 779,788 ****
  		else if (plrv == Py_None)
  		{
  			fcinfo->isnull = true;
! 			rv = InputFunctionCall(&proc->result.out.d.typfunc,
! 								   NULL,
! 								   proc->result.out.d.typioparam,
! 								   -1);
  		}
  		else
  		{
--- 855,893 ----
  		else if (plrv == Py_None)
  		{
  			fcinfo->isnull = true;
! 			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 = NULL;
! 
! 			if (PySequence_Check(plrv))
! 				/* composite type as sequence (tuple, list etc) */
! 				tuple = PLySequence_ToTuple(&proc->result, plrv);
! 			else if (PyMapping_Check(plrv))
! 				/* composite type as mapping (currently only dict) */
! 				tuple = PLyMapping_ToTuple(&proc->result, plrv);
! 			else
! 				/* returned as smth, must provide method __getattr__(name) */
! 				tuple = PLyObject_ToTuple(&proc->result, plrv);
! 
! 			if (tuple != NULL)
! 			{
! 				fcinfo->isnull = false;
! 				rv = HeapTupleGetDatum(tuple);
! 			}
! 			else
! 			{
! 				fcinfo->isnull = true;
! 				rv = (Datum) NULL;
! 			}
  		}
  		else
  		{
***************
*** 911,916 ****
--- 1016,1023 ----
  			 * FIXME -- error check this
  			 */
  			PyList_SetItem(args, i, arg);
+ 			if (proc->argnames)
+ 				PyDict_SetItemString(proc->globals, proc->argnames[i], arg);
  			arg = NULL;
  		}
  	}
***************
*** 927,932 ****
--- 1034,1052 ----
  }
  
  
+ 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
   */
***************
*** 997,1002 ****
--- 1117,1125 ----
  	bool		isnull;
  	int			i,
  				rv;
+ 	Datum		argnames;
+ 	Datum	    	*elems;
+ 	int		nelems;
  
  	procStruct = (Form_pg_proc) GETSTRUCT(procTup);
  
***************
*** 1028,1033 ****
--- 1151,1159 ----
  	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();
  	{
***************
*** 1064,1072 ****
  			}
  
  			if (rvTypeStruct->typtype == 'c')
! 				ereport(ERROR,
! 						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! 					 errmsg("plpython functions cannot return tuples yet")));
  			else
  				PLy_output_datum_func(&proc->result, rvTypeTup);
  
--- 1190,1200 ----
  			}
  
  			if (rvTypeStruct->typtype == 'c')
! 			{
! 				/* 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);
  
***************
*** 1089,1094 ****
--- 1217,1236 ----
  		 * 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;
***************
*** 1117,1124 ****
  				proc->args[i].is_rowtype = 2;	/* still need to set I/O funcs */
  
  			ReleaseSysCache(argTypeTup);
- 		}
  
  
  		/*
  		 * get the text of the function.
--- 1259,1269 ----
  				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.
***************
*** 1254,1259 ****
--- 1399,1405 ----
  	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)
***************
*** 1261,1266 ****
--- 1407,1417 ----
  			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
***************
*** 1519,1524 ****
--- 1670,1894 ----
  	return dict;
  }
  
+ 
+ static HeapTuple
+ PLyMapping_ToTuple(PLyTypeInfo *info, PyObject *mapping)
+ {
+ 	TupleDesc	desc;
+ 	HeapTuple	tuple;
+ 	Datum		*values;
+ 	char		*nulls;
+ 	int		i;
+ 
+ 	Assert(PyMapping_Check(mapping));
+ 
+ 	desc = lookup_rowtype_tupdesc(info->out.d.typoid, -1);
+ 	if (info->is_rowtype == 2)
+ 		PLy_output_tuple_funcs(info, desc);
+ 	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 = PyMapping_GetItemString(mapping, key);
+ 			if (value && value != Py_None)
+ 			{
+ 				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);
+ 				so = NULL;
+ 				nulls[i] = ' ';
+ 			}
+ 			else
+ 			{
+ 				values[i] = (Datum) NULL;
+ 				nulls[i] = 'n';
+ 			}
+ 			Py_XDECREF(value);
+ 			value = NULL;
+ 		}
+ 		PG_CATCH();
+ 		{
+ 			Py_XDECREF(so);
+ 			Py_XDECREF(value);
+ 			PG_RE_THROW();
+ 		}
+ 		PG_END_TRY();
+ 	}
+ 
+ 	tuple = heap_formtuple(desc, values, nulls);
+ 	ReleaseTupleDesc(desc);
+ 	pfree(values);
+ 	pfree(nulls);
+ 
+ 	return tuple;
+ }
+ 
+ 
+ static HeapTuple
+ PLySequence_ToTuple(PLyTypeInfo *info, PyObject *sequence)
+ {
+ 	TupleDesc	desc;
+ 	HeapTuple	tuple;
+ 	Datum		*values;
+ 	char		*nulls;
+ 	int		i;
+ 
+ 	Assert(PySequence_Check(sequence));
+ 
+ 	/*
+ 	 * Check that sequence length is exactly same as PG tuple's. We actually
+ 	 * can ignore exceeding items or assume missing ones as null but to
+ 	 * avoid plpython developer's errors we are strict here
+ 	 */
+ 	desc = lookup_rowtype_tupdesc(info->out.d.typoid, -1);
+ 	if (PySequence_Length(sequence) != desc->natts)
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_DATATYPE_MISMATCH),
+ 				 errmsg("returned sequence's length must be same as tuple's length")));
+ 
+ 	if (info->is_rowtype == 2)
+ 		PLy_output_tuple_funcs(info, desc);
+ 	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)
+ 	{
+ 		PyObject	*value,
+ 				*so;
+ 
+ 		value = so = NULL;
+ 		PG_TRY();
+ 		{
+ 			value = PySequence_GetItem(sequence, i);
+ 			if (value && value != Py_None)
+ 			{
+ 				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);
+ 				so = NULL;
+ 				nulls[i] = ' ';
+ 			}
+ 			else
+ 			{
+ 				values[i] = (Datum) NULL;
+ 				nulls[i] = 'n';
+ 			}
+ 			Py_XDECREF(value);
+ 			value = NULL;
+ 		}
+ 		PG_CATCH();
+ 		{
+ 			Py_XDECREF(so);
+ 			Py_XDECREF(value);
+ 			PG_RE_THROW();
+ 		}
+ 		PG_END_TRY();
+ 	}
+ 
+ 	tuple = heap_formtuple(desc, values, nulls);
+ 	ReleaseTupleDesc(desc);
+ 	pfree(values);
+ 	pfree(nulls);
+ 
+ 	return tuple;
+ }
+ 
+ 
+ static HeapTuple
+ PLyObject_ToTuple(PLyTypeInfo *info, PyObject *object)
+ {
+ 	TupleDesc	desc;
+ 	HeapTuple	tuple;
+ 	Datum		*values;
+ 	char		*nulls;
+ 	int		i;
+ 
+ 	Assert(info->is_rowtype == 1);
+ 
+ 	desc = lookup_rowtype_tupdesc(info->out.d.typoid, -1);
+ 	if (info->is_rowtype == 2)
+ 		PLy_output_tuple_funcs(info, desc);
+ 	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 = PyObject_GetAttrString(object, key);
+ 			if (value && value != Py_None)
+ 			{
+ 				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);
+ 				so = NULL;
+ 				nulls[i] = ' ';
+ 			}
+ 			else
+ 			{
+ 				values[i] = (Datum) NULL;
+ 				nulls[i] = 'n';
+ 			}
+ 			Py_XDECREF(value);
+ 			value = NULL;
+ 		}
+ 		PG_CATCH();
+ 		{
+ 			Py_XDECREF(so);
+ 			Py_XDECREF(value);
+ 			PG_RE_THROW();
+ 		}
+ 		PG_END_TRY();
+ 	}
+ 
+ 	tuple = heap_formtuple(desc, values, nulls);
+ 	ReleaseTupleDesc(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.8
diff -c -r1.8 plpython_function.out
*** expected/plpython_function.out	27 May 2006 12:39:11 -0000	1.8
--- expected/plpython_function.out	18 Jun 2006 09:14:20 -0000
***************
*** 55,81 ****
  	return "failed, that wasn''t supposed to happen"
  return "succeeded, as expected"'
      LANGUAGE plpythonu;
! CREATE FUNCTION import_test_one(text) RETURNS text
  	AS
  'import sha
! digest = sha.new(args[0])
  return digest.hexdigest()'
  	LANGUAGE plpythonu;
! CREATE FUNCTION import_test_two(users) RETURNS text
  	AS
  'import sha
! plain = args[0]["fname"] + args[0]["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
  	AS
! 'keys = args[0].keys()
  keys.sort()
  out = []
  for key in keys:
!     out.append("%s: %s" % (key, args[0][key]))
! words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}"
  return words'
  	LANGUAGE plpythonu;
  -- these triggers are dedicated to HPHC of RI who
--- 55,81 ----
  	return "failed, that wasn''t supposed to happen"
  return "succeeded, as expected"'
      LANGUAGE plpythonu;
! CREATE FUNCTION import_test_one(p text) RETURNS text
  	AS
  'import sha
! digest = sha.new(p)
  return digest.hexdigest()'
  	LANGUAGE plpythonu;
! CREATE FUNCTION import_test_two(u users) RETURNS text
  	AS
  'import sha
! plain = u["fname"] + u["lname"]
  digest = sha.new(plain);
  return "sha hash of " + plain + " is " + digest.hexdigest()'
  	LANGUAGE plpythonu;
! CREATE FUNCTION argument_test_one(u users, a1 text, a2 text) RETURNS text
  	AS
! 'keys = u.keys()
  keys.sort()
  out = []
  for key in keys:
!     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
***************
*** 174,213 ****
  DROP FUNCTION trigger_data();
  -- nested calls
  --
! CREATE FUNCTION nested_call_one(text) RETURNS text
  	AS
! 'q = "SELECT nested_call_two(''%s'')" % args[0]
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
! CREATE FUNCTION nested_call_two(text) RETURNS text
  	AS
! 'q = "SELECT nested_call_three(''%s'')" % args[0]
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
! CREATE FUNCTION nested_call_three(text) RETURNS text
  	AS
! 'return args[0]'
  	LANGUAGE plpythonu ;
  -- some spi stuff
! CREATE FUNCTION spi_prepared_plan_test_one(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"
  except Exception, ex:
  	plpy.error(str(ex))
  return None
  '
  	LANGUAGE plpythonu;
! CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text
  	AS
  'if not SD.has_key("myplan"):
! 	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0]
  	SD["myplan"] = plpy.prepare(q)
  try:
  	rv = plpy.execute(SD["myplan"])
--- 174,213 ----
  DROP FUNCTION trigger_data();
  -- nested calls
  --
! CREATE FUNCTION nested_call_one(a text) RETURNS text
  	AS
! 'q = "SELECT nested_call_two(''%s'')" % a
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
! CREATE FUNCTION nested_call_two(a text) RETURNS text
  	AS
! 'q = "SELECT nested_call_three(''%s'')" % a
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
! CREATE FUNCTION nested_call_three(a text) RETURNS text
  	AS
! 'return a'
  	LANGUAGE plpythonu ;
  -- some spi stuff
! 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"], [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(a text) RETURNS text
  	AS
  'if not SD.has_key("myplan"):
! 	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % a
  	SD["myplan"] = plpy.prepare(q)
  try:
  	rv = plpy.execute(SD["myplan"])
***************
*** 223,234 ****
  CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu;
  /* a typo
  */
! CREATE FUNCTION invalid_type_uncaught(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] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 223,234 ----
  CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu;
  /* a typo
  */
! 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"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 237,243 ****
  /* for what it's worth catch the exception generated by
   * the typo, and return None
   */
! CREATE FUNCTION invalid_type_caught(text) RETURNS text
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
--- 237,243 ----
  /* for what it's worth catch the exception generated by
   * the typo, and return None
   */
! CREATE FUNCTION invalid_type_caught(a text) RETURNS text
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
***************
*** 246,252 ****
  	except plpy.SPIError, ex:
  		plpy.notice(str(ex))
  		return None
! rv = plpy.execute(SD["plan"], [ args[0] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 246,252 ----
  	except plpy.SPIError, ex:
  		plpy.notice(str(ex))
  		return None
! rv = plpy.execute(SD["plan"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 255,261 ****
  /* 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
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
--- 255,261 ----
  /* for what it's worth catch the exception generated by
   * the typo, and reraise it as a plain error
   */
! CREATE FUNCTION invalid_type_reraised(a text) RETURNS text
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
***************
*** 263,269 ****
  		SD["plan"] = plpy.prepare(q, [ "test" ])
  	except plpy.SPIError, ex:
  		plpy.error(str(ex))
! rv = plpy.execute(SD["plan"], [ args[0] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 263,269 ----
  		SD["plan"] = plpy.prepare(q, [ "test" ])
  	except plpy.SPIError, ex:
  		plpy.error(str(ex))
! rv = plpy.execute(SD["plan"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 271,281 ****
  	LANGUAGE plpythonu;
  /* no typo no messing about
  */
! CREATE FUNCTION valid_type(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] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 271,281 ----
  	LANGUAGE plpythonu;
  /* no typo no messing about
  */
! 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"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 300,312 ****
  'rv = plpy.execute("SELECT test5(''foo'')")
  return rv[0]'
  	LANGUAGE plpythonu;
! CREATE FUNCTION join_sequences(sequences) RETURNS text
  	AS
! 'if not args[0]["multipart"]:
! 	return args[0]["sequence"]
! q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"]
  rv = plpy.execute(q)
! seq = args[0]["sequence"]
  for r in rv:
  	seq = seq + r["sequence"]
  return seq
--- 300,312 ----
  'rv = plpy.execute("SELECT test5(''foo'')")
  return rv[0]'
  	LANGUAGE plpythonu;
! CREATE FUNCTION join_sequences(s sequences) RETURNS text
  	AS
! 'if not s["multipart"]:
! 	return s["sequence"]
! q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % s["pid"]
  rv = plpy.execute(q)
! seq = s["sequence"]
  for r in rv:
  	seq = seq + r["sequence"]
  return seq
***************
*** 357,359 ****
--- 357,429 ----
  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_as(typ text, first text, second integer, retnull boolean) RETURNS table_record AS $$
+ if retnull:
+ 	return None
+ if typ == 'dict':
+ 	return {'first': first, 'second': second, 'additionalfield': 'must not cause trouble'}
+ elif typ == 'tuple':
+ 	return (first, second)
+ elif typ == 'list':
+ 	return [ first, second ]
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION test_type_record_as(typ text, first text, second integer, retnull boolean) RETURNS type_record AS $$
+ if retnull:
+ 	return None
+ if typ == 'dict':
+ 	return {'first': first, 'second': second, 'additionalfield': 'must not cause trouble'}
+ elif typ == 'tuple':
+ 	return (first, second)
+ elif typ == 'list':
+ 	return [ first, second ]
+ $$ 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 -c -r1.2 plpython_schema.out
*** expected/plpython_schema.out	10 Jul 2005 04:56:55 -0000	1.2
--- expected/plpython_schema.out	18 Jun 2006 09:14:20 -0000
***************
*** 44,46 ****
--- 44,54 ----
  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 -c -r1.4 plpython_test.out
*** expected/plpython_test.out	1 Mar 2006 21:09:32 -0000	1.4
--- expected/plpython_test.out	18 Jun 2006 09:14:20 -0000
***************
*** 198,200 ****
--- 198,481 ----
                    | 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_as('dict', null, null, false);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('dict', 'one', null, false);
+  first | second 
+ -------+--------
+  one   |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('dict', null, 2, false);
+  first | second 
+ -------+--------
+        |      2
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('dict', 'three', 3, false);
+  first | second 
+ -------+--------
+  three |      3
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('dict', null, null, true);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('tuple', null, null, false);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('tuple', 'one', null, false);
+  first | second 
+ -------+--------
+  one   |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('tuple', null, 2, false);
+  first | second 
+ -------+--------
+        |      2
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('tuple', 'three', 3, false);
+  first | second 
+ -------+--------
+  three |      3
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('tuple', null, null, true);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('list', null, null, false);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('list', 'one', null, false);
+  first | second 
+ -------+--------
+  one   |       
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('list', null, 2, false);
+  first | second 
+ -------+--------
+        |      2
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('list', 'three', 3, false);
+  first | second 
+ -------+--------
+  three |      3
+ (1 row)
+ 
+ SELECT * FROM test_table_record_as('list', null, null, true);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('dict', null, null, false);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('dict', 'one', null, false);
+  first | second 
+ -------+--------
+  one   |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('dict', null, 2, false);
+  first | second 
+ -------+--------
+        |      2
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('dict', 'three', 3, false);
+  first | second 
+ -------+--------
+  three |      3
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('dict', null, null, true);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('tuple', null, null, false);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('tuple', 'one', null, false);
+  first | second 
+ -------+--------
+  one   |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('tuple', null, 2, false);
+  first | second 
+ -------+--------
+        |      2
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('tuple', 'three', 3, false);
+  first | second 
+ -------+--------
+  three |      3
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('tuple', null, null, true);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('list', null, null, false);
+  first | second 
+ -------+--------
+        |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('list', 'one', null, false);
+  first | second 
+ -------+--------
+  one   |       
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('list', null, 2, false);
+  first | second 
+ -------+--------
+        |      2
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('list', 'three', 3, false);
+  first | second 
+ -------+--------
+  three |      3
+ (1 row)
+ 
+ SELECT * FROM test_type_record_as('list', null, null, 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.8
diff -c -r1.8 plpython_function.sql
*** sql/plpython_function.sql	27 May 2006 12:39:11 -0000	1.8
--- sql/plpython_function.sql	18 Jun 2006 09:14:20 -0000
***************
*** 65,93 ****
  return "succeeded, as expected"'
      LANGUAGE plpythonu;
  
! CREATE FUNCTION import_test_one(text) RETURNS text
  	AS
  'import sha
! digest = sha.new(args[0])
  return digest.hexdigest()'
  	LANGUAGE plpythonu;
  
! CREATE FUNCTION import_test_two(users) RETURNS text
  	AS
  'import sha
! plain = args[0]["fname"] + args[0]["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
  	AS
! 'keys = args[0].keys()
  keys.sort()
  out = []
  for key in keys:
!     out.append("%s: %s" % (key, args[0][key]))
! words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}"
  return words'
  	LANGUAGE plpythonu;
  
--- 65,93 ----
  return "succeeded, as expected"'
      LANGUAGE plpythonu;
  
! CREATE FUNCTION import_test_one(p text) RETURNS text
  	AS
  'import sha
! digest = sha.new(p)
  return digest.hexdigest()'
  	LANGUAGE plpythonu;
  
! CREATE FUNCTION import_test_two(u users) RETURNS text
  	AS
  'import sha
! plain = u["fname"] + u["lname"]
  digest = sha.new(plain);
  return "sha hash of " + plain + " is " + digest.hexdigest()'
  	LANGUAGE plpythonu;
  
! CREATE FUNCTION argument_test_one(u users, a1 text, a2 text) RETURNS text
  	AS
! 'keys = u.keys()
  keys.sort()
  out = []
  for key in keys:
!     out.append("%s: %s" % (key, u[key]))
! words = a1 + " " + a2 + " => {" + ", ".join(out) + "}"
  return words'
  	LANGUAGE plpythonu;
  
***************
*** 176,220 ****
  -- nested calls
  --
  
! CREATE FUNCTION nested_call_one(text) RETURNS text
  	AS
! 'q = "SELECT nested_call_two(''%s'')" % args[0]
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
  
! CREATE FUNCTION nested_call_two(text) RETURNS text
  	AS
! 'q = "SELECT nested_call_three(''%s'')" % args[0]
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
  
! CREATE FUNCTION nested_call_three(text) RETURNS text
  	AS
! 'return args[0]'
  	LANGUAGE plpythonu ;
  
  -- some spi stuff
  
! CREATE FUNCTION spi_prepared_plan_test_one(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"
  except Exception, ex:
  	plpy.error(str(ex))
  return None
  '
  	LANGUAGE plpythonu;
  
! CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text
  	AS
  'if not SD.has_key("myplan"):
! 	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0]
  	SD["myplan"] = plpy.prepare(q)
  try:
  	rv = plpy.execute(SD["myplan"])
--- 176,220 ----
  -- nested calls
  --
  
! CREATE FUNCTION nested_call_one(a text) RETURNS text
  	AS
! 'q = "SELECT nested_call_two(''%s'')" % a
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
  
! CREATE FUNCTION nested_call_two(a text) RETURNS text
  	AS
! 'q = "SELECT nested_call_three(''%s'')" % a
  r = plpy.execute(q)
  return r[0]'
  	LANGUAGE plpythonu ;
  
! CREATE FUNCTION nested_call_three(a text) RETURNS text
  	AS
! 'return a'
  	LANGUAGE plpythonu ;
  
  -- some spi stuff
  
! 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"], [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(a text) RETURNS text
  	AS
  'if not SD.has_key("myplan"):
! 	q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % a
  	SD["myplan"] = plpy.prepare(q)
  try:
  	rv = plpy.execute(SD["myplan"])
***************
*** 233,244 ****
  
  /* a typo
  */
! CREATE FUNCTION invalid_type_uncaught(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] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 233,244 ----
  
  /* a typo
  */
! 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"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 248,254 ****
  /* for what it's worth catch the exception generated by
   * the typo, and return None
   */
! CREATE FUNCTION invalid_type_caught(text) RETURNS text
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
--- 248,254 ----
  /* for what it's worth catch the exception generated by
   * the typo, and return None
   */
! CREATE FUNCTION invalid_type_caught(a text) RETURNS text
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
***************
*** 257,263 ****
  	except plpy.SPIError, ex:
  		plpy.notice(str(ex))
  		return None
! rv = plpy.execute(SD["plan"], [ args[0] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 257,263 ----
  	except plpy.SPIError, ex:
  		plpy.notice(str(ex))
  		return None
! rv = plpy.execute(SD["plan"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 267,273 ****
  /* 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
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
--- 267,273 ----
  /* for what it's worth catch the exception generated by
   * the typo, and reraise it as a plain error
   */
! CREATE FUNCTION invalid_type_reraised(a text) RETURNS text
  	AS
  'if not SD.has_key("plan"):
  	q = "SELECT fname FROM users WHERE lname = $1"
***************
*** 275,281 ****
  		SD["plan"] = plpy.prepare(q, [ "test" ])
  	except plpy.SPIError, ex:
  		plpy.error(str(ex))
! rv = plpy.execute(SD["plan"], [ args[0] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 275,281 ----
  		SD["plan"] = plpy.prepare(q, [ "test" ])
  	except plpy.SPIError, ex:
  		plpy.error(str(ex))
! rv = plpy.execute(SD["plan"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 285,295 ****
  
  /* no typo no messing about
  */
! CREATE FUNCTION valid_type(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] ])
  if len(rv):
  	return rv[0]["fname"]
  return None
--- 285,295 ----
  
  /* no typo no messing about
  */
! 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"], [ a ])
  if len(rv):
  	return rv[0]["fname"]
  return None
***************
*** 318,330 ****
  	LANGUAGE plpythonu;
  
  
! CREATE FUNCTION join_sequences(sequences) RETURNS text
  	AS
! 'if not args[0]["multipart"]:
! 	return args[0]["sequence"]
! q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"]
  rv = plpy.execute(q)
! seq = args[0]["sequence"]
  for r in rv:
  	seq = seq + r["sequence"]
  return seq
--- 318,330 ----
  	LANGUAGE plpythonu;
  
  
! CREATE FUNCTION join_sequences(s sequences) RETURNS text
  	AS
! 'if not s["multipart"]:
! 	return s["sequence"]
! q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % s["pid"]
  rv = plpy.execute(q)
! seq = s["sequence"]
  for r in rv:
  	seq = seq + r["sequence"]
  return seq
***************
*** 389,391 ****
--- 389,473 ----
  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_as(typ text, first text, second integer, retnull boolean) RETURNS table_record AS $$
+ if retnull:
+ 	return None
+ if typ == 'dict':
+ 	return {'first': first, 'second': second, 'additionalfield': 'must not cause trouble'}
+ elif typ == 'tuple':
+ 	return (first, second)
+ elif typ == 'list':
+ 	return [ first, second ]
+ $$ LANGUAGE plpythonu;
+ 
+ CREATE FUNCTION test_type_record_as(typ text, first text, second integer, retnull boolean) RETURNS type_record AS $$
+ if retnull:
+ 	return None
+ if typ == 'dict':
+ 	return {'first': first, 'second': second, 'additionalfield': 'must not cause trouble'}
+ elif typ == 'tuple':
+ 	return (first, second)
+ elif typ == 'list':
+ 	return [ first, second ]
+ $$ LANGUAGE plpythonu;
+ 
Index: sql/plpython_schema.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/sql/plpython_schema.sql,v
retrieving revision 1.2
diff -c -r1.2 plpython_schema.sql
*** sql/plpython_schema.sql	10 Jul 2005 04:56:55 -0000	1.2
--- sql/plpython_schema.sql	18 Jun 2006 09:14:20 -0000
***************
*** 42,44 ****
--- 42,54 ----
  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 -c -r1.2 plpython_test.sql
*** sql/plpython_test.sql	28 Feb 2006 20:03:52 -0000	1.2
--- sql/plpython_test.sql	18 Jun 2006 09:14:20 -0000
***************
*** 73,75 ****
--- 73,133 ----
  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_as('dict', null, null, false);
+ SELECT * FROM test_table_record_as('dict', 'one', null, false);
+ SELECT * FROM test_table_record_as('dict', null, 2, false);
+ SELECT * FROM test_table_record_as('dict', 'three', 3, false);
+ SELECT * FROM test_table_record_as('dict', null, null, true);
+ 
+ SELECT * FROM test_table_record_as('tuple', null, null, false);
+ SELECT * FROM test_table_record_as('tuple', 'one', null, false);
+ SELECT * FROM test_table_record_as('tuple', null, 2, false);
+ SELECT * FROM test_table_record_as('tuple', 'three', 3, false);
+ SELECT * FROM test_table_record_as('tuple', null, null, true);
+ 
+ SELECT * FROM test_table_record_as('list', null, null, false);
+ SELECT * FROM test_table_record_as('list', 'one', null, false);
+ SELECT * FROM test_table_record_as('list', null, 2, false);
+ SELECT * FROM test_table_record_as('list', 'three', 3, false);
+ SELECT * FROM test_table_record_as('list', null, null, true);
+ 
+ SELECT * FROM test_type_record_as('dict', null, null, false);
+ SELECT * FROM test_type_record_as('dict', 'one', null, false);
+ SELECT * FROM test_type_record_as('dict', null, 2, false);
+ SELECT * FROM test_type_record_as('dict', 'three', 3, false);
+ SELECT * FROM test_type_record_as('dict', null, null, true);
+ 
+ SELECT * FROM test_type_record_as('tuple', null, null, false);
+ SELECT * FROM test_type_record_as('tuple', 'one', null, false);
+ SELECT * FROM test_type_record_as('tuple', null, 2, false);
+ SELECT * FROM test_type_record_as('tuple', 'three', 3, false);
+ SELECT * FROM test_type_record_as('tuple', null, null, true);
+ 
+ SELECT * FROM test_type_record_as('list', null, null, false);
+ SELECT * FROM test_type_record_as('list', 'one', null, false);
+ SELECT * FROM test_type_record_as('list', null, 2, false);
+ SELECT * FROM test_type_record_as('list', 'three', 3, false);
+ SELECT * FROM test_type_record_as('list', null, null, true);
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to