Hello,

this patch allow any expression in return statement and it does simply casting from non type record into typed records. Allows same casting as assign statement.


Regards
Pavel Stehule


From: Bruce Momjian <[EMAIL PROTECTED]>
To: Pavel Stehule <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: near future plpgsql?
Date: Wed, 23 Aug 2006 08:25:18 -0400 (EDT)

Pavel Stehule wrote:
> Hello,
>
> I had talk with Neil and I knew so Neil forgot on my patch
> http://archives.postgresql.org/pgsql-patches/2005-11/msg00045.php . I want
> to resend this patch again, but this patch is in conflict with cancelled
> "lookahead" patch. Can I work on it? My patch was sent before feature
> frease. But I prefere apply it after "lookahead" patch.
>
> your recommendation?

Yes, please send an updated version.  Sorry we forgot about it.  I try
to keep patches around until someone does apply it, but didn't do that
in this case.

--
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.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-23 14:37:20.000000000 +0200
***************
*** 2001,2006 ****
--- 2001,2007 ----
  make_return_stmt(int lineno)
  {
  	PLpgSQL_stmt_return *new;
+ 	int tok;
  
  	new = palloc0(sizeof(PLpgSQL_stmt_return));
  	new->cmd_type = PLPGSQL_STMT_RETURN;
***************
*** 2026,2032 ****
  	}
  	else if (plpgsql_curr_compile->fn_retistuple)
  	{
! 		switch (yylex())
  		{
  			case K_NULL:
  				/* we allow this to support RETURN NULL in triggers */
--- 2027,2034 ----
  	}
  	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
  	{
--- 2042,2059 ----
  				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 ****
--- 2073,2079 ----
  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");
***************
*** 2083,2089 ****
  	}
  	else if (plpgsql_curr_compile->fn_retistuple)
  	{
! 		switch (yylex())
  		{
  			case T_ROW:
  				new->retvarno = yylval.row->rowno;
--- 2092,2099 ----
  	}
  	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(';', ";");
--- 2103,2120 ----
  				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-23 13:39:06.000000000 +0200
***************
*** 155,160 ****
--- 155,168 ----
  static void exec_set_found(PLpgSQL_execstate *estate, bool state);
  static void free_var(PLpgSQL_var *var);
  
+ static HeapTuple coerce_to_tuple(PLpgSQL_execstate * estate,
+ 				 HeapTuple tuple,
+ 				 TupleDesc tupdesc,
+ 				 TupleDesc reqtupdesc);
+ 
+ static void
+ exec_coerce_tuple(PLpgSQL_execstate *estate);
+ 
  
  /* ----------
   * 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
  		{
--- 1813,1827 ----
  	{
  		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_coerce_tuple(estate);
  		}
  		else
  		{
***************
*** 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);
  	}
--- 1950,1997 ----
  		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)
! 			{
!                 exec_coerce_tuple(estate);
! 				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 ****
--- 4715,4824 ----
  		var->freeval = false;
  	}
  }
+ 
+ 
+ /*
+  * Cast tuple for desired tuple descriptor
+  *
+  */
+ 
+ static void
+ exec_coerce_tuple(PLpgSQL_execstate *estate)
+ {
+     int tupType;
+     int tupTypmod;
+     TupleDesc in_tupdesc;
+     HeapTupleData td;
+ 	
+     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")));
+ 	
+     td.t_len = HeapTupleHeaderGetDatumLength((HeapTupleHeader) estate->retval);
+     ItemPointerSetInvalid(&(td.t_self));
+     td.t_tableOid = InvalidOid;
+     td.t_data = (HeapTupleHeader) estate->retval;
+ 	
+     /* coerce type if needed */
+     
+     if (estate->rsi && IsA(estate->rsi, ReturnSetInfo)
+ 		&& estate->rsi->expectedDesc != NULL
+ 		&& !compatible_tupdesc(estate->rsi->expectedDesc, in_tupdesc))
+ 	{
+ 		
+ 		estate->retval = (Datum) coerce_to_tuple(estate,
+ 												 &td,
+ 												 in_tupdesc,
+ 												 estate->rsi->expectedDesc);
+ 		if ((HeapTuple) estate->retval == NULL)
+ 			ereport(ERROR,
+ 					(errcode(ERRCODE_DATATYPE_MISMATCH),
+ 					 errmsg("wrong record type supplied in RETURN NEXT")));
+ 		
+ 		estate->rettupdesc = estate->rsi->expectedDesc;
+ 	}
+     else
+ 	{
+ 		estate->retval = (Datum) heap_copytuple(&td);;
+ 		estate->rettupdesc = in_tupdesc;
+ 	}
+     
+     ReleaseTupleDesc(in_tupdesc);
+     exec_eval_cleanup(estate);
+ }                 
+ 		 
+ 
+ static HeapTuple
+ coerce_to_tuple(PLpgSQL_execstate * estate,
+ 				HeapTuple tuple,
+ 				TupleDesc tupdesc,
+ 				TupleDesc reqtupdesc)
+ {
+ 	int rnatts = reqtupdesc->natts;
+ 	int natts = tupdesc->natts;
+ 	Datum   *values;
+ 	Datum      *rvalues;
+ 	bool   *nulls;
+ 	bool       *rnulls;
+ 	int i;
+ 	
+ 	values = (Datum *) palloc0(natts * sizeof(Datum));
+ 	rvalues = (Datum *) palloc0(rnatts * sizeof(Datum));
+ 	nulls = (bool *) palloc(natts * sizeof(bool));
+ 	rnulls = (bool *) palloc(rnatts * sizeof(bool));
+ 	
+ 	heap_deform_tuple(tuple, tupdesc, values, nulls);
+ 	
+ 	for (i = 0; i < rnatts; i++)
+     {
+ 		/* coerce if needed */
+ 		if (i < natts)
+ 		{ 
+ 			rvalues[i] = exec_simple_cast_value(values[i], 
+ 												tupdesc->attrs[i]->atttypid,
+ 												reqtupdesc->attrs[i]->atttypid,
+ 												reqtupdesc->attrs[i]->atttypmod,
+ 												nulls[i]);
+ 			rnulls[i] = nulls[i];
+ 		}
+ 		else
+ 		{
+ 			/* missing values */
+ 			rnulls[i] = true;
+ 		}
+ 	}
+   
+ 	tuple = heap_form_tuple(reqtupdesc, rvalues, rnulls);
+ 	
+ 	pfree(values);
+ 	pfree(rvalues);
+ 	pfree(nulls);
+ 	pfree(rnulls);
+ 	
+ 	return tuple;
+ }
*** ./src/test/regress/sql/plpgsql.sql.orig	2006-08-23 14:20:53.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql	2006-08-23 14:35:51.000000000 +0200
***************
*** 2440,2442 ****
--- 2440,2626 ----
  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();
+  
+ 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();
+  
+ -- wrong, unsupported implicit casting
+ 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 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to