Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)

diff -c -r --new-file pgsql.02/doc/src/sgml/func.sgml pgsql/doc/src/sgml/func.sgml
*** pgsql.02/doc/src/sgml/func.sgml	2005-06-06 15:28:59.000000000 +0200
--- pgsql/doc/src/sgml/func.sgml	2005-06-07 00:49:35.000000000 +0200
***************
*** 6805,6810 ****
--- 6805,6855 ----
    </sect2>
  
    <sect2>
+     <title><literal>DECODE</literal></title>
+     
+   <indexterm>
+    <primary>DECODE</primary>
+   </indexterm>
+ 
+ <synopsis>
+ <function>DECODE</function>(<replaceable>expr</replaceable> <replaceable>search, result</replaceable><optional>,<replaceable>search, result</replaceable></optional><optional>, default</optional>)
+ </synopsis>
+ 
+   <para>
+   The first argument to the DECODE function is the expression that you want to decode. 
+   First, compare the value expr to the value of search, and if the values are equal, 
+   DECODE returns the value result. If they're not equal, DECODE try next pair search, 
+   result. If there are not other pair returns NULL else last unmatched argumet - default
+   value. If is possible, use ANSI SQL CASE</para>
+   </sect2>
+   
+   <sect2>
+    <title><literal>GREATEST</literal> and <literal>LEAST</literal></title>
+ 
+   <indexterm>
+    <primary>GREATEST</primary>
+   </indexterm>
+ 
+   <indexterm>
+    <primary>LEAST</primary>
+   </indexterm>
+ 
+ <synopsis>
+ <function>GREATEST</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
+ </synopsis>
+ <synopsis>
+ <function>LEAST</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
+ </synopsis>
+ 
+    <para>
+    The GREATEST and LEAST functions determine the largest and smallest values from multiple 
+    columns or expressions.
+    </para>
+   </sect2>
+   
+       
+ 
+   <sect2>
     <title><literal>NULLIF</></title>
  
    <indexterm>
diff -c -r --new-file pgsql.02/src/backend/executor/execQual.c pgsql/src/backend/executor/execQual.c
*** pgsql.02/src/backend/executor/execQual.c	2005-06-06 15:29:05.000000000 +0200
--- pgsql/src/backend/executor/execQual.c	2005-06-06 23:37:18.000000000 +0200
***************
*** 105,110 ****
--- 105,115 ----
  static Datum ExecEvalCoalesce(CoalesceExprState *coalesceExpr,
  				 ExprContext *econtext,
  				 bool *isNull, ExprDoneCond *isDone);
+ static Datum ExecEvalVarargGreatest(VarargExprState *varargExpr,
+ 				 ExprContext *econtext,
+ 				 bool *isNull, ExprDoneCond *isDone);
+ static Datum ExecEvalVarargDecode(VarargExprState *varargExpr, ExprContext *econtext,
+ 				 bool *isNull, ExprDoneCond *isDone);
  static Datum ExecEvalNullIf(FuncExprState *nullIfExpr,
  			   ExprContext *econtext,
  			   bool *isNull, ExprDoneCond *isDone);
***************
*** 2248,2253 ****
--- 2253,2408 ----
  }
  
  /* ----------------------------------------------------------------
+  *		ExecEvalVarargDecode
+  * ----------------------------------------------------------------
+  */
+ 
+ #define FROM_EXPR_TO_SEARCH 	argtype = IS_SEARCH;
+ #define FROM_SEARCH_TO_RESULT 	argtype = IS_RESULT; 
+ #define FROM_RESULT_TO_SEARCH	argtype = IS_SEARCH; 			    
+ 
+ typedef enum DecodeArgsType
+ {
+     IS_EXPR,
+     IS_SEARCH,
+     IS_RESULT
+ } DecodeArgsType;
+ 
+ 
+ static Datum
+ ExecEvalVarargDecode(VarargExprState *varargExpr, ExprContext *econtext,
+ 				 bool *isNull, ExprDoneCond *isDone)
+ {
+     ListCell *arg;
+     Datum expr;
+     FunctionCallInfoData locfcinfo;
+     TypeCacheEntry *typentry;
+     bool isNullExpr = false;
+     bool found = false;
+     DecodeArgsType argtype = IS_EXPR; 
+ 
+     if (isDone)
+ 	*isDone = ExprSingleResult;
+ 
+     foreach(arg, varargExpr->args)
+     {
+ 	Datum search;  
+         int32		cmpresult;
+         ExprState  *e = (ExprState *) lfirst(arg);
+           
+         switch (argtype)
+ 	{
+ 	    case IS_EXPR:
+ 		expr = ExecEvalExpr(e, econtext, isNull, NULL);
+ 
+ 		if (*isNull)
+ 		    isNullExpr = true;
+ 		else
+ 		{
+ 	    	    typentry = lookup_type_cache(varargExpr->paramtype, TYPECACHE_CMP_PROC_FINFO);
+ 	    	    if (!OidIsValid(typentry->cmp_proc_finfo.fn_oid))
+ 			ereport(ERROR,
+ 			    (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ 				errmsg("could not identify a comparison function for type %s",
+ 				    format_type_be(varargExpr->paramtype))));
+ 	      
+ 	    	    InitFunctionCallInfoData(locfcinfo, &typentry->cmp_proc_finfo, 2,
+ 					       NULL, NULL);   
+ 	    	    locfcinfo.argnull[0] = false;
+ 	    	    locfcinfo.argnull[1] = false;
+ 	    	    locfcinfo.isnull = false;
+ 	      
+ 	    	    locfcinfo.arg[0] = expr;
+ 		}
+ 		FROM_EXPR_TO_SEARCH;
+ 		break;
+ 	    
+ 	    case IS_SEARCH:
+ 	    {
+ 		search = ExecEvalExpr(e, econtext, isNull, NULL);
+ 		if (lnext(arg) == NULL) /* Is default? */
+ 		    return search;
+ 		if (*isNull && isNullExpr) 
+ 		    found = true;
+ 		if (isNullExpr == false && *isNull == false)
+ 		{
+ 		    locfcinfo.arg[1] = search;
+ 		    cmpresult = DatumGetInt32(FunctionCallInvoke(&locfcinfo));
+ 		    if (cmpresult == 0)
+ 			found = true;
+ 		}
+ 		FROM_SEARCH_TO_RESULT;
+ 		break;
+ 	    }
+ 	    case IS_RESULT: /* only if is result and found */
+ 		if (found)
+ 		    return ExecEvalExpr(e, econtext, isNull, NULL);
+ 		FROM_RESULT_TO_SEARCH;
+ 		break;
+ 	}
+     }      
+     *isNull = true;
+     return (Datum) 0;
+ }
+ 
+ /* ----------------------------------------------------------------
+  *		ExecEvalVarargGreatest
+  * ----------------------------------------------------------------
+  */
+ 
+ static Datum
+ ExecEvalVarargGreatest(VarargExprState *varargExpr, ExprContext *econtext,
+ 				 bool *isNull, ExprDoneCond *isDone)
+ {
+     ListCell *arg;
+     Datum result = (Datum) 0;
+     TypeCacheEntry *typentry;
+     FunctionCallInfoData locfcinfo;    
+ 
+     if (isDone)
+       *isDone = ExprSingleResult;
+ 
+     typentry = lookup_type_cache(varargExpr->varargtype, TYPECACHE_CMP_PROC_FINFO);
+     
+     if (!OidIsValid(typentry->cmp_proc_finfo.fn_oid))
+         ereport(ERROR,
+ 	      (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ 	    	    errmsg("could not identify a comparison function for type %s",
+ 		        format_type_be(varargExpr->varargtype))));
+     
+     InitFunctionCallInfoData(locfcinfo, &typentry->cmp_proc_finfo, 2,
+ 			     NULL, NULL);   
+     locfcinfo.argnull[0] = false;
+     locfcinfo.argnull[1] = false;
+     locfcinfo.isnull = false;
+ 
+     foreach(arg, varargExpr->args)
+     {
+ 	int32		cmpresult;
+         ExprState  *e = (ExprState *) lfirst(arg);
+         Datum value = ExecEvalExpr(e, econtext, isNull, NULL);
+         if (*isNull)
+ 	    return value;
+         if (result) 
+ 	{
+ 	    locfcinfo.arg[0] = result;
+ 	    locfcinfo.arg[1] = value;
+ 	    cmpresult = DatumGetInt32(FunctionCallInvoke(&locfcinfo));
+ 
+ 	    if (cmpresult > 0 && varargExpr->type == IS_LEAST)
+ 		result = value;
+ 	    else if (cmpresult < 0 && varargExpr->type == IS_GREATEST)
+ 		result = value;
+ 	}
+     	else
+ 	    result = value;
+     }
+     *isNull = result == 0;
+     return result;
+ }
+ 
+ 
+ /* ----------------------------------------------------------------
   *		ExecEvalNullIf
   *
   * Note that this is *always* derived from the equals operator,
***************
*** 3206,3211 ****
--- 3361,3400 ----
  				state = (ExprState *) cstate;
  			}
  			break;
+ 		case T_VarargExpr:
+ 			{
+ 				VarargExpr *varargexpr = (VarargExpr *) node;
+ 				VarargExprState *vstate = makeNode(VarargExprState);
+ 				List	   *outlist = NIL;
+ 				ListCell   *l;
+ 				
+ 				switch(varargexpr->type)
+ 				{
+ 				    case IS_GREATEST:
+ 				    case IS_LEAST:
+ 					vstate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalVarargGreatest;
+ 					
+ 					break;
+ 				    case IS_DECODE:
+ 					vstate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalVarargDecode;
+ 					vstate->paramtype = varargexpr->paramtype;
+ 					break;
+ 				}
+ 				
+ 				foreach(l, varargexpr->args)
+ 				{
+ 					Expr	   *e = (Expr *) lfirst(l);
+ 					ExprState  *estate;
+ 
+ 					estate = ExecInitExpr(e, parent);
+ 					outlist = lappend(outlist, estate);
+ 				}
+ 				vstate->args = outlist;
+ 				vstate->varargtype = varargexpr->varargtype;
+ 				vstate->type = varargexpr->type;
+ 				state = (ExprState *) vstate;
+ 			}
+ 			break;
  		case T_NullIfExpr:
  			{
  				NullIfExpr *nullifexpr = (NullIfExpr *) node;
diff -c -r --new-file pgsql.02/src/backend/nodes/copyfuncs.c pgsql/src/backend/nodes/copyfuncs.c
*** pgsql.02/src/backend/nodes/copyfuncs.c	2005-06-06 15:29:07.000000000 +0200
--- pgsql/src/backend/nodes/copyfuncs.c	2005-06-06 23:33:19.000000000 +0200
***************
*** 1048,1053 ****
--- 1048,1071 ----
  }
  
  /*
+  * _copyVarargExpr
+  */
+   
+ static VarargExpr *
+ _copyVarargExpr(VarargExpr *from)
+ {
+         VarargExpr *newnode = makeNode(VarargExpr);
+ 	  
+ 	COPY_SCALAR_FIELD(varargtype);
+ 	COPY_SCALAR_FIELD(paramtype);
+ 	COPY_SCALAR_FIELD(type);
+ 	COPY_NODE_FIELD(args);
+ 					  
+         return newnode;
+ }
+ 						  
+ 
+ /*
   * _copyNullIfExpr (same as OpExpr)
   */
  static NullIfExpr *
***************
*** 2817,2822 ****
--- 2835,2843 ----
  		case T_CoalesceExpr:
  			retval = _copyCoalesceExpr(from);
  			break;
+ 		case T_VarargExpr:
+ 		        retval = _copyVarargExpr(from);
+ 			break;								
  		case T_NullIfExpr:
  			retval = _copyNullIfExpr(from);
  			break;
diff -c -r --new-file pgsql.02/src/backend/nodes/equalfuncs.c pgsql/src/backend/nodes/equalfuncs.c
*** pgsql.02/src/backend/nodes/equalfuncs.c	2005-06-06 15:29:07.000000000 +0200
--- pgsql/src/backend/nodes/equalfuncs.c	2005-06-06 23:31:14.000000000 +0200
***************
*** 451,456 ****
--- 451,467 ----
  }
  
  static bool
+ _equalVarargExpr(VarargExpr *a, VarargExpr *b)
+ {
+ 	COMPARE_SCALAR_FIELD(varargtype);
+ 	COMPARE_SCALAR_FIELD(paramtype);
+ 	COMPARE_SCALAR_FIELD(type);
+ 	COMPARE_NODE_FIELD(args);
+ 
+ 	return true;
+ }
+ 		    
+ static bool
  _equalNullIfExpr(NullIfExpr *a, NullIfExpr *b)
  {
  	COMPARE_SCALAR_FIELD(opno);
***************
*** 1875,1880 ****
--- 1886,1894 ----
  		case T_CoalesceExpr:
  			retval = _equalCoalesceExpr(a, b);
  			break;
+ 		case T_VarargExpr:
+ 			retval = _equalVarargExpr(a, b);
+ 			break;							
  		case T_NullIfExpr:
  			retval = _equalNullIfExpr(a, b);
  			break;
diff -c -r --new-file pgsql.02/src/backend/nodes/outfuncs.c pgsql/src/backend/nodes/outfuncs.c
*** pgsql.02/src/backend/nodes/outfuncs.c	2005-06-06 15:29:07.000000000 +0200
--- pgsql/src/backend/nodes/outfuncs.c	2005-06-06 23:28:31.000000000 +0200
***************
*** 865,870 ****
--- 865,881 ----
  }
  
  static void
+ _outVarargExpr(StringInfo str, VarargExpr *node)
+ {
+         WRITE_NODE_TYPE("VARARG");
+ 	
+ 	WRITE_OID_FIELD(varargtype);
+ 	WRITE_OID_FIELD(paramtype);
+ 	WRITE_ENUM_FIELD(type, VarargExprType);
+ 	WRITE_NODE_FIELD(args);
+ }
+ 					
+ static void
  _outNullIfExpr(StringInfo str, NullIfExpr *node)
  {
  	WRITE_NODE_TYPE("NULLIFEXPR");
***************
*** 1904,1909 ****
--- 1915,1923 ----
  			case T_CoalesceExpr:
  				_outCoalesceExpr(str, obj);
  				break;
+ 			case T_VarargExpr:
+ 			        _outVarargExpr(str, obj);
+ 				break;							
  			case T_NullIfExpr:
  				_outNullIfExpr(str, obj);
  				break;
diff -c -r --new-file pgsql.02/src/backend/nodes/readfuncs.c pgsql/src/backend/nodes/readfuncs.c
*** pgsql.02/src/backend/nodes/readfuncs.c	2005-06-06 15:29:07.000000000 +0200
--- pgsql/src/backend/nodes/readfuncs.c	2005-06-06 23:29:17.000000000 +0200
***************
*** 659,664 ****
--- 659,680 ----
  }
  
  /*
+  * _readVarargExpr
+  */
+ static VarargExpr *
+ _readVarargExpr(void)
+ {
+         READ_LOCALS(VarargExpr);
+       
+         READ_OID_FIELD(varargtype);
+ 	READ_OID_FIELD(paramtype);
+ 	READ_ENUM_FIELD(type,VarargExprType);
+ 	READ_NODE_FIELD(args);
+ 		      
+ 	READ_DONE();
+ }
+ 			  
+ /*
   * _readNullIfExpr
   */
  static NullIfExpr *
***************
*** 982,987 ****
--- 998,1005 ----
  		return_value = _readRowExpr();
  	else if (MATCH("COALESCE", 8))
  		return_value = _readCoalesceExpr();
+         else if (MATCH("VARARG",6))
+ 	        return_value = _readVarargExpr();			       
  	else if (MATCH("NULLIFEXPR", 10))
  		return_value = _readNullIfExpr();
  	else if (MATCH("NULLTEST", 8))
diff -c -r --new-file pgsql.02/src/backend/optimizer/util/clauses.c pgsql/src/backend/optimizer/util/clauses.c
*** pgsql.02/src/backend/optimizer/util/clauses.c	2005-06-06 15:29:09.000000000 +0200
--- pgsql/src/backend/optimizer/util/clauses.c	2005-06-06 23:25:00.000000000 +0200
***************
*** 542,547 ****
--- 542,549 ----
  		return false;
  	if (IsA(node, CoalesceExpr))
  		return false;
+         if (IsA(node, VarargExpr))
+ 	  return false;
  	if (IsA(node, NullIfExpr))
  		return false;
  
***************
*** 847,852 ****
--- 849,856 ----
  		return true;
  	if (IsA(node, CoalesceExpr))
  		return true;
+         if (IsA(node, VarargExpr))
+ 	  return true;
  	if (IsA(node, NullIfExpr))
  		return true;
  	if (IsA(node, NullTest))
***************
*** 1796,1801 ****
--- 1800,1836 ----
  		newcoalesce->args = newargs;
  		return (Node *) newcoalesce;
  	}
+ 	if (IsA(node, VarargExpr))
+ 	{
+ 		VarargExpr *varargexpr = (VarargExpr *) node;
+ 		VarargExpr *newvararg;
+ 		List	   *newargs;
+ 		ListCell   *arg;
+ 
+ 		newargs = NIL;
+ 		
+ 		foreach(arg, varargexpr->args)
+ 		{
+ 			Node	   *e;
+ 			e = eval_const_expressions_mutator((Node *) lfirst(arg),
+     										   context);
+ 			/* If any argument is null, then result is null (for GREATEST and LEAST)*/
+ 			if (IsA(e, Const))
+ 			{
+ 				if (((Const *) e)->constisnull && 
+ 					(varargexpr->type == IS_GREATEST || varargexpr->type == IS_LEAST))
+ 					    return (Node *) makeNullConst(varargexpr->varargtype);
+ 			}
+ 			newargs = lappend(newargs, e);
+ 		}
+ 
+ 		newvararg = makeNode(VarargExpr);
+ 		newvararg->varargtype = varargexpr->varargtype;
+ 		newvararg->type = varargexpr->type;
+ 		newvararg->paramtype = varargexpr->paramtype;
+ 		newvararg->args = newargs;
+ 		return (Node *) newvararg;
+ 	}
  	if (IsA(node, FieldSelect))
  	{
  		/*
***************
*** 2932,2937 ****
--- 2967,2974 ----
  			return walker(((RowExpr *) node)->args, context);
  		case T_CoalesceExpr:
  			return walker(((CoalesceExpr *) node)->args, context);
+ 		case T_VarargExpr:
+ 			return walker(((VarargExpr *) node)->args, context);
  		case T_NullIfExpr:
  			return walker(((NullIfExpr *) node)->args, context);
  		case T_NullTest:
***************
*** 3392,3397 ****
--- 3429,3444 ----
  				return (Node *) newnode;
  			}
  			break;
+ 		case T_VarargExpr:
+ 			{
+ 				VarargExpr *varargexpr = (VarargExpr *) node;
+ 				VarargExpr *newnode;
+ 
+ 				FLATCOPY(newnode, varargexpr, VarargExpr);
+ 				MUTATE(newnode->args, varargexpr->args, List *);
+ 				return (Node *) newnode;
+ 			}
+ 			break;
  		case T_NullIfExpr:
  			{
  				NullIfExpr *expr = (NullIfExpr *) node;
diff -c -r --new-file pgsql.02/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y
*** pgsql.02/src/backend/parser/gram.y	2005-06-06 15:29:09.000000000 +0200
--- pgsql/src/backend/parser/gram.y	2005-06-06 23:19:32.000000000 +0200
***************
*** 350,356 ****
  	CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
  	CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
  
! 	DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
  	DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
  	DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
  
--- 350,356 ----
  	CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
  	CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
  
! 	DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DECODE DEFAULT DEFAULTS
  	DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS
  	DESC DISTINCT DO DOMAIN_P DOUBLE_P DROP
  
***************
*** 360,366 ****
  	FALSE_P FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
  	FREEZE FROM FULL FUNCTION
  
! 	GLOBAL GRANT GROUP_P
  
  	HANDLER HAVING HEADER HOLD HOUR_P
  
--- 360,366 ----
  	FALSE_P FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
  	FREEZE FROM FULL FUNCTION
  
! 	GLOBAL GRANT GREATEST GROUP_P
  
  	HANDLER HAVING HEADER HOLD HOUR_P
  
***************
*** 373,379 ****
  
  	KEY
  
! 	LANCOMPILER LANGUAGE LARGE_P LAST_P LEADING LEFT LEVEL LIKE LIMIT
  	LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
  	LOCK_P
  
--- 373,379 ----
  
  	KEY
  
! 	LANCOMPILER LANGUAGE LARGE_P  LAST_P LEADING LEAST LEFT LEVEL LIKE LIMIT
  	LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
  	LOCK_P
  
***************
*** 7067,7072 ****
--- 7067,7098 ----
  					c->args = $3;
  					$$ = (Node *)c;
  				}
+                         | GREATEST '(' expr_list ')'
+ 			        {
+ 		                        VarargExpr *v = makeNode(VarargExpr);
+ 		                        v->args = $3;
+ 		                        v->type = IS_GREATEST;
+ 		                        $$ = (Node *)v;
+ 				}
+ 		        | LEAST  '(' expr_list ')'
+ 				{
+ 					VarargExpr *v = makeNode(VarargExpr);
+ 					v->args = $3;
+ 					v->type = IS_LEAST;
+ 					$$ = (Node *)v;
+ 				}																													 
+         		| DECODE '(' expr_list ')'
+ 	                        {
+                                         if (list_length($3) < 3)
+ 		                            ereport(ERROR,
+                                                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ 		                                        errmsg("Function Decode needs minimal three arguments")));
+ 
+             				VarargExpr *v = makeNode(VarargExpr);
+             				v->args = $3;
+             				v->type = IS_DECODE;
+         				$$ = (Node *)v;
+             			}																																											    					    								
  		;
  
  /*
***************
*** 7937,7949 ****
--- 7963,7978 ----
  			| CONVERT
  			| DEC
  			| DECIMAL_P
+ 			| DECODE
  			| EXISTS
  			| EXTRACT
  			| FLOAT_P
+ 			| GREATEST
  			| INOUT
  			| INT_P
  			| INTEGER
  			| INTERVAL
+ 			| LEAST
  			| NATIONAL
  			| NCHAR
  			| NONE
diff -c -r --new-file pgsql.02/src/backend/parser/keywords.c pgsql/src/backend/parser/keywords.c
*** pgsql.02/src/backend/parser/keywords.c	2005-06-06 15:29:09.000000000 +0200
--- pgsql/src/backend/parser/keywords.c	2005-06-06 23:45:19.000000000 +0200
***************
*** 103,108 ****
--- 103,109 ----
  	{"dec", DEC},
  	{"decimal", DECIMAL_P},
  	{"declare", DECLARE},
+ 	{"decode", DECODE},
  	{"default", DEFAULT},
  	{"defaults", DEFAULTS},
  	{"deferrable", DEFERRABLE},
***************
*** 145,150 ****
--- 146,152 ----
  	{"function", FUNCTION},
  	{"global", GLOBAL},
  	{"grant", GRANT},
+ 	{"greatest", GREATEST},
  	{"group", GROUP_P},
  	{"handler", HANDLER},
  	{"having", HAVING},
***************
*** 183,188 ****
--- 185,191 ----
  	{"large", LARGE_P},
  	{"last", LAST_P},
  	{"leading", LEADING},
+ 	{"least", LEAST},
  	{"left", LEFT},
  	{"level", LEVEL},
  	{"like", LIKE},
diff -c -r --new-file pgsql.02/src/backend/parser/parse_expr.c pgsql/src/backend/parser/parse_expr.c
*** pgsql.02/src/backend/parser/parse_expr.c	2005-06-06 15:29:10.000000000 +0200
--- pgsql/src/backend/parser/parse_expr.c	2005-06-06 23:39:15.000000000 +0200
***************
*** 53,58 ****
--- 53,59 ----
  static Node *transformArrayExpr(ParseState *pstate, ArrayExpr *a);
  static Node *transformRowExpr(ParseState *pstate, RowExpr *r);
  static Node *transformCoalesceExpr(ParseState *pstate, CoalesceExpr *c);
+ static Node *transformVarargExpr(ParseState *pstate, VarargExpr *v);
  static Node *transformBooleanTest(ParseState *pstate, BooleanTest *b);
  static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref);
  static Node *transformWholeRowRef(ParseState *pstate, char *schemaname,
***************
*** 209,214 ****
--- 210,219 ----
  			result = transformCoalesceExpr(pstate, (CoalesceExpr *) expr);
  			break;
  
+ 		case T_VarargExpr:
+                         result = transformVarargExpr(pstate, (VarargExpr *) expr);
+ 		        break;
+ 						
  		case T_NullTest:
  			{
  				NullTest   *n = (NullTest *) expr;
***************
*** 1229,1234 ****
--- 1234,1374 ----
  	return (Node *) newc;
  }
  
+ #define FROM_EXPR_TO_SEARCH     argtype = IS_SEARCH;
+ #define FROM_SEARCH_TO_RESULT   argtype = IS_RESULT;
+ #define FROM_RESULT_TO_SEARCH   argtype = IS_SEARCH;
+ 
+ typedef enum DecodeArgsType
+ {
+ 	IS_EXPR,
+         IS_SEARCH,
+ 	IS_RESULT
+ } DecodeArgsType;
+ 
+ 	    
+ static Node *
+ transformVarargExpr(ParseState *pstate, VarargExpr *v)
+ {
+ 	VarargExpr *newva = makeNode(VarargExpr);
+ 	List	   *newargs = NIL;
+ 	List	   *newcoercedargs = NIL;
+ 	List	   *typeids = NIL;
+ 	ListCell   *args;
+ 	List *searchtypeids = NIL;
+ 
+ 	newva->type = v->type;
+ 
+ 	switch (v->type)
+ 	{
+ 		case IS_DECODE:
+ 		{
+ 			DecodeArgsType argtype = IS_EXPR;
+ 			foreach(args, v->args)
+ 			{
+ 			    Node	   *e = (Node *) lfirst(args);
+ 			    Node	   *newe;
+ 
+ 			    newe = transformExpr(pstate, e);
+ 			    newargs = lappend(newargs, newe);
+ 
+ 			    if (lnext(args) == NULL && argtype == IS_SEARCH)
+ 				argtype = IS_RESULT;
+ 			    
+ 			    switch (argtype)
+ 			    {
+ 				case IS_EXPR:
+ 				    searchtypeids = lappend_oid(searchtypeids, exprType(newe));
+ 				    FROM_EXPR_TO_SEARCH;
+ 				    break;
+ 				case IS_RESULT:
+     				    typeids = lappend_oid(typeids, exprType(newe));
+ 				    FROM_RESULT_TO_SEARCH;
+ 				    break;
+ 				case IS_SEARCH:
+ 				    searchtypeids = lappend_oid(searchtypeids, exprType(newe));
+ 				    FROM_SEARCH_TO_RESULT;
+ 				    break;
+ 			    }
+ 				    
+ 			}
+ 			newva->varargtype = select_common_type(typeids, "VARARG");
+ 			newva->paramtype = select_common_type(searchtypeids, "VARARG");
+ 
+ 			/* Convert arguments if necessary */
+ 			argtype = IS_EXPR;
+ 			foreach(args, newargs)
+ 			{
+ 			    Node	   *e = (Node *) lfirst(args);
+ 			    Node	   *newe;
+ 			    
+ 			    if (lnext(args) == NULL && argtype == IS_SEARCH)
+ 				argtype = IS_RESULT;
+ 			    switch (argtype)
+ 			    {    
+ 				case IS_EXPR:
+ 				    newe = coerce_to_common_type(pstate, e,
+ 									 newva->paramtype,
+ 									 "VARARG");
+ 				    FROM_EXPR_TO_SEARCH;
+ 				    break;
+ 				case IS_RESULT:
+ 				    newe = coerce_to_common_type(pstate, e,
+ 									 newva->varargtype,
+ 									 "VARARG");
+ 				    FROM_RESULT_TO_SEARCH;
+ 				    break;
+ 				case IS_SEARCH:
+ 				    newe = coerce_to_common_type(pstate, e,
+ 									 newva->paramtype,
+ 									 "VARARG");
+ 				    FROM_SEARCH_TO_RESULT;
+ 				    break;
+ 			    }
+ 			    
+ 			    newcoercedargs = lappend(newcoercedargs, newe);
+ 			}
+ 			newva->args = newcoercedargs;
+ 			
+ 			break;
+ 		}
+ 		case IS_GREATEST:
+ 		case IS_LEAST:
+ 		{
+ 			foreach(args, v->args)
+ 			{
+ 			    Node	   *e = (Node *) lfirst(args);
+ 			    Node	   *newe;
+ 
+ 			    newe = transformExpr(pstate, e);
+ 			    newargs = lappend(newargs, newe);
+ 			    typeids = lappend_oid(typeids, exprType(newe));
+ 			}
+ 
+ 			newva->varargtype = select_common_type(typeids, "VARARG");
+ 
+ 			/* Convert arguments if necessary */
+ 			foreach(args, newargs)
+ 			{
+ 			    Node	   *e = (Node *) lfirst(args);
+ 			    Node	   *newe;
+ 
+ 			    newe = coerce_to_common_type(pstate, e,
+ 									 newva->varargtype,
+ 									 "VARARG");
+ 			    newcoercedargs = lappend(newcoercedargs, newe);
+ 			}
+ 
+ 			newva->args = newcoercedargs;
+ 			
+ 			break;
+ 		}
+ 			
+ 	}
+ 	return (Node *) newva;
+ }
+ 	    
+ 
+ 
  static Node *
  transformBooleanTest(ParseState *pstate, BooleanTest *b)
  {
***************
*** 1503,1508 ****
--- 1643,1651 ----
  		case T_CoalesceExpr:
  			type = ((CoalesceExpr *) expr)->coalescetype;
  			break;
+ 		case T_VarargExpr:
+ 			type = ((VarargExpr *) expr)->varargtype;
+ 			break;
  		case T_NullIfExpr:
  			type = exprType((Node *) linitial(((NullIfExpr *) expr)->args));
  			break;
***************
*** 1637,1642 ****
--- 1780,1845 ----
  				return typmod;
  			}
  			break;
+ 		case T_VarargExpr:
+ 			{
+ 				/*
+ 				 * If all the alternatives agree on type/typmod, return
+ 				 * that typmod, else use -1
+ 				 */
+ 				VarargExpr *vexpr = (VarargExpr *) expr;
+ 				Oid			varargtype = vexpr->varargtype;
+ 				int32		typmod;
+ 				ListCell   *arg;
+ 				DecodeArgsType argtype = IS_EXPR;
+ 				bool firstResult = true; 
+ 				bool isResult = true;
+ 				
+ 				/* for decode function is usefull only results */
+ 				
+ 				if (vexpr->type != IS_DECODE)
+ 				    typmod = exprTypmod((Node *) linitial(vexpr->args));
+ 				
+ 				foreach(arg, vexpr->args)
+ 				{
+ 					Node	   *e = (Node *) lfirst(arg);
+ 
+ 					if (vexpr->type == IS_DECODE)
+ 					{
+ 					    switch (argtype)
+ 					    {
+ 					    
+ 						case IS_EXPR:
+ 						    FROM_EXPR_TO_SEARCH
+ 						    isResult = false;
+ 						    break;
+ 						case IS_SEARCH:
+ 						    if (lnext(arg) != NULL)
+ 						    {
+ 							isResult = false;
+ 							FROM_SEARCH_TO_RESULT
+ 							break;
+ 						    }
+ 						case IS_RESULT:
+ 						    if (firstResult) 
+ 						    {
+ 							firstResult = false;
+ 							typmod = exprTypmod((Node *) e);
+ 						    }
+ 						    isResult = true;
+ 						    FROM_RESULT_TO_SEARCH
+ 					    }
+ 					}
+ 					if (isResult)
+ 					{
+ 					    if (exprType(e) != varargtype)
+ 						    return -1;
+ 					    if (exprTypmod(e) != typmod)
+ 						    return -1;
+ 					}
+ 				}
+ 				return typmod;
+ 			}
+ 			break;
  		case T_NullIfExpr:
  			{
  				NullIfExpr *nexpr = (NullIfExpr *) expr;
diff -c -r --new-file pgsql.02/src/backend/parser/parse_target.c pgsql/src/backend/parser/parse_target.c
*** pgsql.02/src/backend/parser/parse_target.c	2005-06-06 15:29:10.000000000 +0200
--- pgsql/src/backend/parser/parse_target.c	2005-06-06 23:00:35.000000000 +0200
***************
*** 1123,1128 ****
--- 1123,1142 ----
  			/* make coalesce() act like a regular function */
  			*name = "coalesce";
  			return 2;
+            case T_VarargExpr:
+                         switch (((VarargExpr*) node)->type)
+ 			{
+ 			       case IS_GREATEST:
+ 			           *name = "greatest";
+ 			           return 2;
+ 			       case IS_LEAST:
+ 			           *name = "least";
+ 			           return 2;
+ 			       case IS_DECODE:
+ 			           *name = "decode";
+ 			           return 2;
+ 			}
+ 																					       		
  		default:
  			break;
  	}
diff -c -r --new-file pgsql.02/src/backend/utils/adt/ruleutils.c pgsql/src/backend/utils/adt/ruleutils.c
*** pgsql.02/src/backend/utils/adt/ruleutils.c	2005-06-06 15:29:19.000000000 +0200
--- pgsql/src/backend/utils/adt/ruleutils.c	2005-06-06 22:57:30.000000000 +0200
***************
*** 2781,2786 ****
--- 2781,2787 ----
  		case T_ArrayExpr:
  		case T_RowExpr:
  		case T_CoalesceExpr:
+ 		case T_VarargExpr:
  		case T_NullIfExpr:
  		case T_Aggref:
  		case T_FuncExpr:
***************
*** 2888,2893 ****
--- 2889,2895 ----
  				case T_ArrayExpr:		/* other separators */
  				case T_RowExpr:	/* other separators */
  				case T_CoalesceExpr:	/* own parentheses */
+ 				case T_VarargExpr:	/* own parentheses */
  				case T_NullIfExpr:		/* other separators */
  				case T_Aggref:	/* own parentheses */
  				case T_CaseExpr:		/* other separators */
***************
*** 2935,2940 ****
--- 2937,2943 ----
  				case T_ArrayExpr:		/* other separators */
  				case T_RowExpr:	/* other separators */
  				case T_CoalesceExpr:	/* own parentheses */
+ 				case T_VarargExpr:	/* own parentheses */
  				case T_NullIfExpr:		/* other separators */
  				case T_Aggref:	/* own parentheses */
  				case T_CaseExpr:		/* other separators */
***************
*** 3491,3496 ****
--- 3494,3520 ----
  			}
  			break;
  
+         	case T_VarargExpr:
+                        {
+                     		VarargExpr *varargexpr = (VarargExpr *) node;
+    
+                     		switch (varargexpr->type)
+                                 {
+                         	    case IS_GREATEST:
+ 				            appendStringInfo(buf, "GREATEST(");
+ 					    break;
+ 				    case IS_LEAST:
+ 					    appendStringInfo(buf, "LEAST(");
+ 					    break;
+                                     case IS_DECODE:
+                                             appendStringInfo(buf, "DECODE(");
+                                             break;
+                                 }
+                                 get_rule_expr((Node *) varargexpr->args, context, true);
+                                 appendStringInfoChar(buf, ')');
+ 			}
+ 			break;
+ 																																																		
  		case T_NullIfExpr:
  			{
  				NullIfExpr *nullifexpr = (NullIfExpr *) node;
diff -c -r --new-file pgsql.02/src/include/nodes/execnodes.h pgsql/src/include/nodes/execnodes.h
*** pgsql.02/src/include/nodes/execnodes.h	2005-06-06 15:29:42.000000000 +0200
--- pgsql/src/include/nodes/execnodes.h	2005-06-06 22:48:01.000000000 +0200
***************
*** 672,677 ****
--- 672,692 ----
  } CoalesceExprState;
  
  /* ----------------
+  *              VarargExprState node
+  * ----------------
+  */
+ typedef struct VarargExprState
+ {
+        ExprState       xprstate;
+        VarargExprType type;
+        Oid     varargtype;                     /* type of arguments and result */
+        Oid     paramtype;                      /* type of params */
+        List       *args;                       /* the arguments */
+ } VarargExprState;
+ 					   
+ 					   
+ 
+ /* ----------------
   *		CoerceToDomainState node
   * ----------------
   */
diff -c -r --new-file pgsql.02/src/include/nodes/nodes.h pgsql/src/include/nodes/nodes.h
*** pgsql.02/src/include/nodes/nodes.h	2005-06-06 15:29:42.000000000 +0200
--- pgsql/src/include/nodes/nodes.h	2005-06-06 22:49:09.000000000 +0200
***************
*** 136,141 ****
--- 136,142 ----
  	T_RangeTblRef,
  	T_JoinExpr,
  	T_FromExpr,
+ 	T_VarargExpr,
  
  	/*
  	 * TAGS FOR EXPRESSION STATE NODES (execnodes.h)
***************
*** 161,166 ****
--- 162,168 ----
  	T_CoalesceExprState,
  	T_CoerceToDomainState,
  	T_DomainConstraintState,
+ 	T_VarargExprState,
  
  	/*
  	 * TAGS FOR PLANNER NODES (relation.h)
diff -c -r --new-file pgsql.02/src/include/nodes/primnodes.h pgsql/src/include/nodes/primnodes.h
*** pgsql.02/src/include/nodes/primnodes.h	2005-06-06 15:29:42.000000000 +0200
--- pgsql/src/include/nodes/primnodes.h	2005-06-06 22:50:27.000000000 +0200
***************
*** 657,662 ****
--- 657,683 ----
  	List	   *args;			/* the arguments */
  } CoalesceExpr;
  
+ 
+ /*
+  * VarargExpr - a GREATEST, LEAST expression
+  */
+   
+ typedef enum VarargExprType
+ {
+       IS_GREATEST,
+       IS_LEAST,
+       IS_DECODE
+ } VarargExprType;
+ 	      
+ typedef struct VarargExpr
+ {
+       Expr            xpr;
+       Oid             varargtype;
+       Oid             paramtype;
+       VarargExprType  type;
+       List    *args;
+ } VarargExpr;
+ 						      
  /*
   * NullIfExpr - a NULLIF expression
   *
diff -c -r --new-file pgsql.02/src/test/regress/expected/oracle.out pgsql/src/test/regress/expected/oracle.out
*** pgsql.02/src/test/regress/expected/oracle.out	1970-01-01 01:00:00.000000000 +0100
--- pgsql/src/test/regress/expected/oracle.out	2005-06-07 00:55:32.000000000 +0200
***************
*** 0 ****
--- 1,74 ----
+ SELECT least(1,10,20,30);
+  least 
+ -------
+      1
+ (1 row)
+ 
+ SELECT least('a','b','c','d');
+  least 
+ -------
+  a
+ (1 row)
+ 
+ SELECT least('2004-05-22'::date, '2004-05-10'::date);
+    least    
+ ------------
+  2004-05-10
+ (1 row)
+ 
+ SELECT greatest(1,10,20,30);
+  greatest 
+ ----------
+        30
+ (1 row)
+ 
+ SELECT greatest('a','b','c','d');
+  greatest 
+ ----------
+  d
+ (1 row)
+ 
+ SELECT greatest('2004-05-22'::date, '2004-05-10'::date);
+   greatest  
+ ------------
+  2004-05-22
+ (1 row)
+ 
+ SELECT decode('a','n',10,'m',20,'a',30);
+  decode 
+ --------
+      30
+ (1 row)
+ 
+ SELECT decode('a','n');
+ ERROR:  Function Decode needs minimal three arguments
+ SELECT decode('a','n',10,'m',20,'o',30,40);
+  decode 
+ --------
+      40
+ (1 row)
+ 
+ SELECT decode(2,1,'2004-01-01'::date,2,'2004-04-01'::date,3,'2004-07-01'::date);
+    decode   
+ ------------
+  2004-04-01
+ (1 row)
+ 
+ SELECT decode(null,1,'2004-01-01'::date,2,'2004-04-01'::date,3,'2004-07-01'::date);
+  decode 
+ --------
+  
+ (1 row)
+ 
+ SELECT decode(4,1,'2004-01-01'::date,2,'2004-04-01'::date,3,'2004-07-01'::date);
+  decode 
+ --------
+  
+ (1 row)
+ 
+ SELECT decode(null,'a','a',null,'b');
+  decode 
+ --------
+  b
+ (1 row)
+ 
diff -c -r --new-file pgsql.02/src/test/regress/sql/oracle.sql pgsql/src/test/regress/sql/oracle.sql
*** pgsql.02/src/test/regress/sql/oracle.sql	1970-01-01 01:00:00.000000000 +0100
--- pgsql/src/test/regress/sql/oracle.sql	2005-06-07 00:52:13.000000000 +0200
***************
*** 0 ****
--- 1,15 ----
+ SELECT least(1,10,20,30);
+ SELECT least('a','b','c','d');
+ SELECT least('2004-05-22'::date, '2004-05-10'::date);
+ 
+ SELECT greatest(1,10,20,30);
+ SELECT greatest('a','b','c','d');
+ SELECT greatest('2004-05-22'::date, '2004-05-10'::date);
+ 
+ SELECT decode('a','n',10,'m',20,'a',30);
+ SELECT decode('a','n');
+ SELECT decode('a','n',10,'m',20,'o',30,40);
+ SELECT decode(2,1,'2004-01-01'::date,2,'2004-04-01'::date,3,'2004-07-01'::date);
+ SELECT decode(null,1,'2004-01-01'::date,2,'2004-04-01'::date,3,'2004-07-01'::date);
+ SELECT decode(4,1,'2004-01-01'::date,2,'2004-04-01'::date,3,'2004-07-01'::date);
+ SELECT decode(null,'a','a',null,'b');