Hello
This patch contains three oracle users missing functions. But I
hope can be usefull for all PostgreSQL users (users vote me ;-) Certainly
LEAST and GREATEST, which has not analogy. Using of DECODE is similar
CASE, but with some differences. There exist some workarounds in plpgsql,
but are ugly and neefective, or impossible (function DECODE rotate type of
args). All functions share code.
David, please, can you enhance documentation?
pokus=# select least(1,2,3,4);
least
-------
1
(1 row)
pokus=# select greatest(1,2,3,4);
greatest
----------
4
(1 row)
pokus=# select decode('c','a',2,1);
decode
--------
1
Best regards
Pavel Stehule
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');
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
