Hello

This patch allows using any row expression in return statement and does transformation from untyped row to composite types if it's necessary.


Regards
Pavel Stehule

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig	2006-08-23 14:29:07.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml	2006-08-23 14:31:43.000000000 +0200
***************
*** 1586,1599 ****
       </para>
  
       <para>
-       When returning a scalar type, any expression can be used. The
-       expression's result will be automatically cast into the
-       function's return type as described for assignments. To return a
-       composite (row) value, you must write a record or row variable
-       as the <replaceable>expression</replaceable>.
-      </para>
- 
-      <para>
        If you declared the function with output parameters, write just
        <command>RETURN</command> with no expression.  The current values
        of the output parameter variables will be returned.
--- 1586,1591 ----
*** ./src/pl/plpgsql/src/gram.y.orig	2006-08-23 10:39:35.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y	2006-08-24 13:49:51.000000000 +0200
***************
*** 2001,2010 ****
--- 2001,2013 ----
  make_return_stmt(int lineno)
  {
  	PLpgSQL_stmt_return *new;
+ 	int tok;
  
  	new = palloc0(sizeof(PLpgSQL_stmt_return));
  	new->cmd_type = PLPGSQL_STMT_RETURN;
  	new->lineno   = lineno;
+ 	new->compat_tupdesc = PLPGSQL_TUPLE_DESC_UNKNOWN;
+ 	new->pcache       = NULL;
  	new->expr	  = NULL;
  	new->retvarno = -1;
  
***************
*** 2026,2032 ****
  	}
  	else if (plpgsql_curr_compile->fn_retistuple)
  	{
! 		switch (yylex())
  		{
  			case K_NULL:
  				/* we allow this to support RETURN NULL in triggers */
--- 2029,2036 ----
  	}
  	else if (plpgsql_curr_compile->fn_retistuple)
  	{
! 		tok = yylex();
! 		switch (tok)
  		{
  			case K_NULL:
  				/* we allow this to support RETURN NULL in triggers */
***************
*** 2040,2051 ****
  				new->retvarno = yylval.rec->recno;
  				break;
  
  			default:
! 				yyerror("RETURN must specify a record or row variable in function returning tuple");
  				break;
  		}
! 		if (yylex() != ';')
! 			yyerror("RETURN must specify a record or row variable in function returning tuple");
  	}
  	else
  	{
--- 2044,2061 ----
  				new->retvarno = yylval.rec->recno;
  				break;
  
+ 			case T_WORD:
+ 			case '(':
+ 				plpgsql_push_back_token(tok);
+ 				new->expr = plpgsql_read_expression(';',";");
+ 				break;
+ 				
  			default:
! 				yyerror("RETURN must specify a value in function returning tuple");
  				break;
  		}
! 		if (!new->expr && yylex() != ';')
! 			yyerror("RETURN must specify a value in function returning tuple");
  	}
  	else
  	{
***************
*** 2065,2070 ****
--- 2075,2081 ----
  make_return_next_stmt(int lineno)
  {
  	PLpgSQL_stmt_return_next *new;
+ 	int tok;
  
  	if (!plpgsql_curr_compile->fn_retset)
  		yyerror("cannot use RETURN NEXT in a non-SETOF function");
***************
*** 2072,2077 ****
--- 2083,2090 ----
  	new = palloc0(sizeof(PLpgSQL_stmt_return_next));
  	new->cmd_type	= PLPGSQL_STMT_RETURN_NEXT;
  	new->lineno		= lineno;
+ 	new->compat_tupdesc = PLPGSQL_TUPLE_DESC_UNKNOWN;
+ 	new->pcache   		= NULL;
  	new->expr		= NULL;
  	new->retvarno	= -1;
  
***************
*** 2083,2089 ****
  	}
  	else if (plpgsql_curr_compile->fn_retistuple)
  	{
! 		switch (yylex())
  		{
  			case T_ROW:
  				new->retvarno = yylval.row->rowno;
--- 2096,2103 ----
  	}
  	else if (plpgsql_curr_compile->fn_retistuple)
  	{
! 		tok = yylex();
! 		switch (tok)
  		{
  			case T_ROW:
  				new->retvarno = yylval.row->rowno;
***************
*** 2093,2104 ****
  				new->retvarno = yylval.rec->recno;
  				break;
  
  			default:
! 				yyerror("RETURN NEXT must specify a record or row variable in function returning tuple");
  				break;
  		}
! 		if (yylex() != ';')
! 			yyerror("RETURN NEXT must specify a record or row variable in function returning tuple");
  	}
  	else
  		new->expr = plpgsql_read_expression(';', ";");
--- 2107,2124 ----
  				new->retvarno = yylval.rec->recno;
  				break;
  
+ 			case T_WORD:
+ 			case '(':
+ 				plpgsql_push_back_token(tok);
+ 				new->expr = plpgsql_read_expression(';',";");
+ 				break;
+ 
  			default:
! 				yyerror("RETURN NEXT must a value in function returning tuple");
  				break;
  		}
! 		if (!new->expr && yylex() != ';')
! 			yyerror("RETURN NEXT must specify a value in function returning tuple");
  	}
  	else
  		new->expr = plpgsql_read_expression(';', ";");
*** ./src/pl/plpgsql/src/pl_exec.c.orig	2006-08-23 11:30:40.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c	2006-08-24 16:39:05.000000000 +0200
***************
*** 155,160 ****
--- 155,162 ----
  static void exec_set_found(PLpgSQL_execstate *estate, bool state);
  static void free_var(PLpgSQL_var *var);
  
+ static void exec_return_coerce_tuple(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt);
+ 
  
  /* ----------
   * plpgsql_exec_function	Called by the call handler for
***************
*** 1805,1817 ****
  	{
  		if (estate->retistuple)
  		{
! 			exec_run_select(estate, stmt->expr, 1, NULL);
! 			if (estate->eval_processed > 0)
! 			{
! 				estate->retval = (Datum) estate->eval_tuptable->vals[0];
! 				estate->rettupdesc = estate->eval_tuptable->tupdesc;
! 				estate->retisnull = false;
! 			}
  		}
  		else
  		{
--- 1807,1822 ----
  	{
  		if (estate->retistuple)
  		{
! 			estate->retval = exec_eval_expr(estate, stmt->expr, &(estate->retisnull), &(estate->rettype));
! 			
! 			if (!(estate->rettype == RECORDOID || get_typtype(estate->rettype) == 'c'))
! 				ereport(ERROR,
! 						(errcode(ERRCODE_DATATYPE_MISMATCH),
! 						 errmsg("wrong result type supplied in RETURN")));
! 			
! 
! 			if (!estate->retisnull)
! 				exec_return_coerce_tuple(estate, stmt);
  		}
  		else
  		{
***************
*** 1863,1868 ****
--- 1868,1874 ----
  	if (estate->tuple_store == NULL)
  		exec_init_tuple_store(estate);
  
+ 
  	/* rettupdesc will be filled by exec_init_tuple_store */
  	tupdesc = estate->rettupdesc;
  	natts = tupdesc->natts;
***************
*** 1940,1965 ****
  		bool		isNull;
  		Oid			rettype;
  
! 		if (natts != 1)
! 			ereport(ERROR,
! 					(errcode(ERRCODE_DATATYPE_MISMATCH),
! 					 errmsg("wrong result type supplied in RETURN NEXT")));
! 
! 		retval = exec_eval_expr(estate,
! 								stmt->expr,
! 								&isNull,
! 								&rettype);
! 
! 		/* coerce type if needed */
! 		retval = exec_simple_cast_value(retval,
! 										rettype,
! 										tupdesc->attrs[0]->atttypid,
! 										tupdesc->attrs[0]->atttypmod,
! 										isNull);
! 
! 		tuple = heap_form_tuple(tupdesc, &retval, &isNull);
! 
! 		free_tuple = true;
  
  		exec_eval_cleanup(estate);
  	}
--- 1946,1993 ----
  		bool		isNull;
  		Oid			rettype;
  
! 		if (estate->retistuple)
! 		{
! 			estate->retval = exec_eval_expr(estate, stmt->expr, &(estate->retisnull), &(estate->rettype));
! 			
! 			if (!(estate->rettype == RECORDOID || get_typtype(estate->rettype) == 'c'))
! 				ereport(ERROR,
! 						(errcode(ERRCODE_DATATYPE_MISMATCH),
! 						 errmsg("wrong result type supplied in RETURN")));
! 		 
! 			tuple = NULL;
! 
! 			if (!estate->retisnull)
! 			{
! 				/* coerce type if needed */
! 				exec_return_coerce_tuple(estate, (PLpgSQL_stmt_return *) stmt);
! 				tuple = (HeapTuple) estate->retval;
! 				free_tuple = true;
! 			}
! 		}
! 		else
! 		{
! 			if (natts != 1)
! 				ereport(ERROR,
! 						(errcode(ERRCODE_DATATYPE_MISMATCH),
! 						 errmsg("wrong result type supplied in RETURN NEXT")));
! 			
! 			retval = exec_eval_expr(estate,
! 									stmt->expr,
! 									&isNull,
! 									&rettype);
! 
! 			/* coerce type if needed */
! 			retval = exec_simple_cast_value(retval,
! 											rettype,
! 											tupdesc->attrs[0]->atttypid,
! 											tupdesc->attrs[0]->atttypmod,
! 											isNull);
! 
! 			tuple = heap_form_tuple(tupdesc, &retval, &isNull);
! 		
! 			free_tuple = true;
! 		}
  
  		exec_eval_cleanup(estate);
  	}
***************
*** 4683,4685 ****
--- 4711,4863 ----
  		var->freeval = false;
  	}
  }
+ 
+ 
+ /*
+  * Cast tuple for desired RSI tuple descriptor
+  *
+  */
+ 
+ static HeapTuple
+ coerce_to_tuple(PLpgSQL_execstate * estate,
+ 				HeapTuple tuple,
+ 				TupleDesc tupdesc,
+ 				TupleDesc reqtupdesc,
+ 				PLpgSQL_stmt_return *stmt)
+ {
+ 	int rnatts = reqtupdesc->natts;
+ 	int natts = tupdesc->natts;
+ 	PLpgSQL_cast_tuple_cache *cache = stmt->pcache;	
+ 	int i;
+ 
+ 	if (!cache)
+ 	{
+ 		MemoryContext oldcontext;
+ 
+ 		oldcontext = MemoryContextSwitchTo(estate->err_func->fn_cxt);
+ 		
+ 		cache = palloc(sizeof(PLpgSQL_cast_tuple_cache)); 
+ 		cache->values  = (Datum *) palloc0(natts * sizeof(Datum));
+ 		cache->rvalues = (Datum *) palloc0(rnatts * sizeof(Datum));
+ 		cache->nulls   = (bool *) palloc(natts * sizeof(bool));
+ 		cache->rnulls  = (bool *) palloc(rnatts * sizeof(bool));
+ 
+ 		MemoryContextSwitchTo(oldcontext);
+ 		
+ 		stmt->pcache = cache;
+ 	}
+ 	else
+ 	{	
+ 		memset(cache->values, 0, natts * sizeof(Datum));
+ 		memset(cache->rvalues, 0, rnatts * sizeof(bool));
+ 	}
+ 	
+ 	heap_deform_tuple(tuple, tupdesc, cache->values, cache->nulls);
+ 	
+ 	for (i = 0; i < rnatts; i++)
+ 	{
+ 		if (i < natts)
+ 		{ 
+ 			cache->rvalues[i] = exec_simple_cast_value(cache->values[i], 
+ 												tupdesc->attrs[i]->atttypid,
+ 												reqtupdesc->attrs[i]->atttypid,
+ 												reqtupdesc->attrs[i]->atttypmod,
+ 												cache->nulls[i]);
+ 			cache->rnulls[i] = cache->nulls[i];
+ 		}
+ 		else
+ 		{
+ 			/* missing values */
+ 			cache->rnulls[i] = true;
+ 		}
+ 	}
+   
+ 	tuple = heap_form_tuple(reqtupdesc, cache->rvalues, cache->rnulls);
+ 		
+ 	return tuple;
+ }
+ 
+ static void
+ exec_return_coerce_tuple(PLpgSQL_execstate *estate,
+ 			PLpgSQL_stmt_return *stmt)
+ {
+ 	int tupType;
+ 	int tupTypmod;
+ 	TupleDesc in_tupdesc = NULL;
+ 	HeapTupleData td;
+ 
+ 	td.t_len = HeapTupleHeaderGetDatumLength((HeapTupleHeader) estate->retval);
+ 	ItemPointerSetInvalid(&(td.t_self));
+ 	td.t_tableOid = InvalidOid;
+ 	td.t_data = (HeapTupleHeader) estate->retval;
+ 
+ 	/*
+ 	 * if not neccessery I don't create new in_tupdesc 
+ 	 */
+ 	
+ 	switch (stmt->compat_tupdesc)
+ 	{
+ 		case PLPGSQL_TUPLE_DESC_UNKNOWN:
+ 		case PLPGSQL_TUPLE_DESC_NOTRSI:
+ 		case PLPGSQL_TUPLE_DESC_INCOMPATIBLE:
+ 		{
+ 			tupType = HeapTupleHeaderGetTypeId((HeapTupleHeader) estate->retval);
+ 			tupTypmod = HeapTupleHeaderGetTypMod((HeapTupleHeader) estate->retval);
+ 			in_tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod);
+ 
+ 			if (in_tupdesc == NULL)
+ 				ereport(ERROR,
+ 						(errcode(ERRCODE_DATATYPE_MISMATCH),
+ 						 errmsg("wrong result type supplied in RETURN")));
+ 
+ 			if (stmt->compat_tupdesc == PLPGSQL_TUPLE_DESC_UNKNOWN)
+ 			{
+ 				if (!estate->rsi)
+ 				{
+ 					stmt->compat_tupdesc = PLPGSQL_TUPLE_DESC_NOTRSI;
+ 				}
+ 				else
+ 				{
+ 					stmt->compat_tupdesc = (IsA(estate->rsi, ReturnSetInfo)
+ 								   && estate->rsi->expectedDesc != NULL
+ 								   && compatible_tupdesc(estate->rsi->expectedDesc, in_tupdesc)) ?
+ 								   PLPGSQL_TUPLE_DESC_COMPATIBLE : PLPGSQL_TUPLE_DESC_INCOMPATIBLE;
+ 				}
+ 			}
+ 
+ 			if (stmt->compat_tupdesc == PLPGSQL_TUPLE_DESC_INCOMPATIBLE)
+ 			{
+ 				estate->retval = (Datum) coerce_to_tuple(estate,
+ 														 &td,
+ 														 in_tupdesc,
+ 														 estate->rsi->expectedDesc,
+ 														 stmt);
+ 				estate->rettupdesc = estate->rsi->expectedDesc;
+ 				
+ 				break;
+ 			}
+ 			else if (stmt->compat_tupdesc == PLPGSQL_TUPLE_DESC_NOTRSI)
+ 			{
+ 				estate->retval = (Datum) heap_copytuple(&td);
+ 				estate->rettupdesc = in_tupdesc;
+ 				
+ 				break;
+ 			}
+ 		}
+ 		case PLPGSQL_TUPLE_DESC_COMPATIBLE:
+ 		{
+ 			estate->retval = (Datum) heap_copytuple(&td);
+ 			estate->rettupdesc = estate->rsi->expectedDesc;
+ 		}				
+ 	}
+ 	if ((HeapTuple) estate->retval == NULL)
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_DATATYPE_MISMATCH),
+ 				 errmsg("wrong record type supplied in RETURN NEXT")));
+ 	if (in_tupdesc)			
+ 		ReleaseTupleDesc(in_tupdesc);
+ 
+ 	exec_eval_cleanup(estate);
+ }                 
+ 
+ 
*** ./src/pl/plpgsql/src/plpgsql.h.orig	2006-08-15 21:01:17.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h	2006-08-24 16:10:57.000000000 +0200
***************
*** 117,122 ****
--- 117,135 ----
  	PLPGSQL_GETDIAG_RESULT_OID
  };
  
+ /* ---------
+  * Used for output's coercing of result tuple
+  * ---------
+  */
+  
+ enum
+ {
+ 	PLPGSQL_TUPLE_DESC_COMPATIBLE,
+ 	PLPGSQL_TUPLE_DESC_INCOMPATIBLE,
+ 	PLPGSQL_TUPLE_DESC_NOTRSI,
+ 	PLPGSQL_TUPLE_DESC_UNKNOWN
+ };
+ 	
  
  /**********************************************************************
   * Node and structure definitions
***************
*** 466,476 ****
--- 479,498 ----
  	PLpgSQL_expr *cond;
  } PLpgSQL_stmt_exit;
  
+ typedef struct
+ {
+ 	Datum		*values;
+ 	Datum		*rvalues;
+ 	bool	*nulls;
+ 	bool	*rnulls;
+ } PLpgSQL_cast_tuple_cache;
  
  typedef struct
  {								/* RETURN statement			*/
  	int			cmd_type;
  	int			lineno;
+ 	int			compat_tupdesc;
+ 	PLpgSQL_cast_tuple_cache	*pcache;		/* cache for deform_... */
  	PLpgSQL_expr *expr;
  	int			retvarno;
  } PLpgSQL_stmt_return;
***************
*** 479,487 ****
--- 501,512 ----
  {								/* RETURN NEXT statement */
  	int			cmd_type;
  	int			lineno;
+ 	int			compat_tupdesc;
+ 	PLpgSQL_cast_tuple_cache	*pcache;		/* cache for heap_deform_tuple */
  	PLpgSQL_expr *expr;
  	int			retvarno;
  } PLpgSQL_stmt_return_next;
+ /* offset for compat_tupdesc and pcache have to be equal with PLpgSQL_stmt_return */
  
  typedef struct
  {								/* RAISE statement			*/
*** ./src/test/regress/sql/plpgsql.sql.orig	2006-08-23 14:20:53.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql	2006-08-24 16:43:34.000000000 +0200
***************
*** 2440,2442 ****
--- 2440,2627 ----
  select footest();
  
  drop function footest();
+ 
+ -- test return can contain any valid row expression
+ 
+ 
+ create type __trt as (x integer, y integer, z text);
+  
+ create or replace function return_row1() returns __trt as $$ 
+ declare r __trt;
+ begin r := row(1,2,3);
+   return r;
+ end;
+ $$ language plpgsql;
+ select return_row1();
+ 
+ -- should fail, record variable isn't cast to __trt type 
+ create or replace function return_row2() returns __trt as $$ 
+ declare r record;
+ begin r := row(1,2,3);
+   return r;
+ end;
+ $$ language plpgsql;
+ select return_row2();
+ 
+ -- should fail, missing rsi for neccessery conversion
+ create or replace function return_row3() returns __trt as $$ 
+ begin 
+   return row(1,2,3);
+ end;
+ $$ language plpgsql;
+ select return_row3();
+  
+ create or replace function return_row4() returns __trt as $$ 
+ begin
+   return (1,2,'3'::text);
+ end;
+ $$ language plpgsql;
+ select return_row4();
+  
+ create or replace function return_row5() returns record as $$ 
+ begin
+   return row(1,2,3);
+ end;
+ $$ language plpgsql;
+ select return_row5();
+  
+ create or replace function return_row6() returns setof __trt as $$ 
+ begin
+   return next row(1,2,3::text);
+   return next row(4,5,6::text);
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row6();
+  
+ create or replace function return_row7() returns setof __trt as $$ 
+ declare r __trt;
+ begin
+   r := row(1,2,3);
+   return next r;
+   r := row(4,5,6);
+   return next r;
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row7();
+  
+ create or replace function return_row8() returns setof __trt as $$ 
+ declare r record;
+ begin
+   r := row(1,2,3::text);
+   return next r;
+   r := row(4,5,6::text);
+   return next r;
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row8();
+  
+ create or replace function return_row9() returns setof record as $$ 
+ declare r record;
+ begin
+   r := row(1,2,3::text);
+   return next r;
+   r := row(4,5,6::text);
+   return next r;
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row9() as (x integer, y integer, z text);
+  
+ create or replace function return_row10() returns setof record as $$ 
+ begin
+   return next row(1,2,3::text);
+   return next row(4,5,6::text);
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row10() as (x integer, y integer, z text);
+  
+ create or replace function return_row11() returns setof float as $$ 
+ declare a float = 1.3;
+ begin
+   return next sin(1.1);
+   return next sin(1.2);
+   return next sin(a);
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row11();
+  
+ create or replace function return_row12() returns float as $$
+ declare a float = 1.2;
+ begin
+   return sin(a);
+ end;
+ $$ language plpgsql;
+ select return_row12();
+  
+ -- should fail: only row function is allowed
+ create or replace function return_row13() returns setof record as $$ 
+ begin
+   return next sin(1);
+   return next sin(2);
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row13() as (x integer, y integer, z text);
+  
+ create or replace function return_row14() returns setof record as $$
+ begin
+   return next (1,2,3);
+   return next (1,2,3::text);
+   return next row(1,2,3::text);
+   return next return_row5();
+   return next return_row1();
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row14() as (x integer, y integer, z text);
+  
+ create or replace function return_row15() returns record as $$
+ begin
+   return return_row4();
+ end;
+ $$ language plpgsql;
+ select return_row15();
+ select * from return_row15() as (a integer, b integer, c text);
+  
+ create or replace function return_row16() returns record as $$
+ begin
+   return return_row4();
+ end;
+ $$ language plpgsql;
+ select return_row16();
+ select * from return_row16() as (a integer, b integer, c text);
+ 
+ create or replace function return_row17() returns setof record as $$
+ begin
+   return next (1,2,3, NULL, NULL);
+   return next (1,2,3::text, NULL, NULL);
+   return next row(1,2,3::text,4,5);
+   return;
+ end;
+ $$ language plpgsql;
+ select * from return_row17() as (x integer, y integer, z integer, i integer);
+  
+ drop function return_row1();
+ drop function return_row2();
+ drop function return_row3();
+ drop function return_row4();
+ drop function return_row5();
+ drop function return_row6();
+ drop function return_row7();
+ drop function return_row8();
+ drop function return_row9 ();
+ drop function return_row10();
+ drop function return_row11();
+ drop function return_row12();
+ drop function return_row13();
+ drop function return_row14();
+ drop function return_row15();
+ drop function return_row16();
+ drop function return_row17();
+  
+ drop type __trt;

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to