diff -c -r --new-file pgsql.02/doc/src/sgml/func.sgml pgsql.03/doc/src/sgml/func.sgml *** pgsql.02/doc/src/sgml/func.sgml 2005-06-06 15:28:59.000000000 +0200 --- pgsql.03/doc/src/sgml/func.sgml 2005-06-07 23:57:58.000000000 +0200 *************** *** 6805,6810 **** --- 6805,6836 ---- + <literal>GREATEST</literal> and <literal>LEAST</literal> + + + GREATEST + + + + LEAST + + + + GREATEST(value , ...) + + + LEAST(value , ...) + + + + The GREATEST and LEAST functions determine the largest and smallest values from multiple + columns or expressions. + + + + + + <literal>NULLIF</> diff -c -r --new-file pgsql.02/src/backend/executor/execQual.c pgsql.03/src/backend/executor/execQual.c *** pgsql.02/src/backend/executor/execQual.c 2005-06-06 15:29:05.000000000 +0200 --- pgsql.03/src/backend/executor/execQual.c 2005-06-07 23:25:57.000000000 +0200 *************** *** 105,110 **** --- 105,113 ---- static Datum ExecEvalCoalesce(CoalesceExprState *coalesceExpr, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); + static Datum ExecEvalVarargGreatest(VarargExprState *varargExpr, + ExprContext *econtext, + bool *isNull, ExprDoneCond *isDone); static Datum ExecEvalNullIf(FuncExprState *nullIfExpr, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); *************** *** 2248,2253 **** --- 2251,2313 ---- } /* ---------------------------------------------------------------- + * 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 **** --- 3266,3294 ---- state = (ExprState *) cstate; } break; + case T_VarargExpr: + { + VarargExpr *varargexpr = (VarargExpr *) node; + VarargExprState *vstate = makeNode(VarargExprState); + List *outlist = NIL; + ListCell *l; + + vstate->xprstate.evalfunc = (ExprStateEvalFunc) ExecEvalVarargGreatest; + + 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.03/src/backend/nodes/copyfuncs.c *** pgsql.02/src/backend/nodes/copyfuncs.c 2005-06-06 15:29:07.000000000 +0200 --- pgsql.03/src/backend/nodes/copyfuncs.c 2005-06-07 23:27:24.000000000 +0200 *************** *** 1048,1053 **** --- 1048,1070 ---- } /* + * _copyVarargExpr + */ + + static VarargExpr * + _copyVarargExpr(VarargExpr *from) + { + VarargExpr *newnode = makeNode(VarargExpr); + + COPY_SCALAR_FIELD(varargtype); + COPY_SCALAR_FIELD(type); + COPY_NODE_FIELD(args); + + return newnode; + } + + + /* * _copyNullIfExpr (same as OpExpr) */ static NullIfExpr * *************** *** 2817,2822 **** --- 2834,2842 ---- 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.03/src/backend/nodes/equalfuncs.c *** pgsql.02/src/backend/nodes/equalfuncs.c 2005-06-06 15:29:07.000000000 +0200 --- pgsql.03/src/backend/nodes/equalfuncs.c 2005-06-07 23:27:38.000000000 +0200 *************** *** 451,456 **** --- 451,466 ---- } static bool + _equalVarargExpr(VarargExpr *a, VarargExpr *b) + { + COMPARE_SCALAR_FIELD(varargtype); + COMPARE_SCALAR_FIELD(type); + COMPARE_NODE_FIELD(args); + + return true; + } + + static bool _equalNullIfExpr(NullIfExpr *a, NullIfExpr *b) { COMPARE_SCALAR_FIELD(opno); *************** *** 1875,1880 **** --- 1885,1893 ---- 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.03/src/backend/nodes/outfuncs.c *** pgsql.02/src/backend/nodes/outfuncs.c 2005-06-06 15:29:07.000000000 +0200 --- pgsql.03/src/backend/nodes/outfuncs.c 2005-06-07 23:28:05.000000000 +0200 *************** *** 865,870 **** --- 865,880 ---- } static void + _outVarargExpr(StringInfo str, VarargExpr *node) + { + WRITE_NODE_TYPE("VARARG"); + + WRITE_OID_FIELD(varargtype); + WRITE_ENUM_FIELD(type, VarargExprType); + WRITE_NODE_FIELD(args); + } + + static void _outNullIfExpr(StringInfo str, NullIfExpr *node) { WRITE_NODE_TYPE("NULLIFEXPR"); *************** *** 1904,1909 **** --- 1914,1922 ---- 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.03/src/backend/nodes/readfuncs.c *** pgsql.02/src/backend/nodes/readfuncs.c 2005-06-06 15:29:07.000000000 +0200 --- pgsql.03/src/backend/nodes/readfuncs.c 2005-06-07 23:57:02.000000000 +0200 *************** *** 659,664 **** --- 659,679 ---- } /* + * _readVarargExpr + */ + static VarargExpr * + _readVarargExpr(void) + { + READ_LOCALS(VarargExpr); + + READ_OID_FIELD(varargtype); + READ_ENUM_FIELD(type,VarargExprType); + READ_NODE_FIELD(args); + + READ_DONE(); + } + + /* * _readNullIfExpr */ static NullIfExpr * *************** *** 982,987 **** --- 997,1004 ---- 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.03/src/backend/optimizer/util/clauses.c *** pgsql.02/src/backend/optimizer/util/clauses.c 2005-06-06 15:29:09.000000000 +0200 --- pgsql.03/src/backend/optimizer/util/clauses.c 2005-06-07 23:49:08.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,1834 ---- 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 ) + return (Node *) makeNullConst(varargexpr->varargtype); + } + newargs = lappend(newargs, e); + } + + newvararg = makeNode(VarargExpr); + newvararg->varargtype = varargexpr->varargtype; + newvararg->type = varargexpr->type; + newvararg->args = newargs; + return (Node *) newvararg; + } if (IsA(node, FieldSelect)) { /* *************** *** 2932,2937 **** --- 2965,2972 ---- 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 **** --- 3427,3442 ---- 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.03/src/backend/parser/gram.y *** pgsql.02/src/backend/parser/gram.y 2005-06-06 15:29:09.000000000 +0200 --- pgsql.03/src/backend/parser/gram.y 2005-06-07 23:18:32.000000000 +0200 *************** *** 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,7086 ---- 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; + } ; /* *************** *** 7940,7949 **** --- 7954,7965 ---- | 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.03/src/backend/parser/keywords.c *** pgsql.02/src/backend/parser/keywords.c 2005-06-06 15:29:09.000000000 +0200 --- pgsql.03/src/backend/parser/keywords.c 2005-06-07 23:18:43.000000000 +0200 *************** *** 145,150 **** --- 145,151 ---- {"function", FUNCTION}, {"global", GLOBAL}, {"grant", GRANT}, + {"greatest", GREATEST}, {"group", GROUP_P}, {"handler", HANDLER}, {"having", HAVING}, *************** *** 183,188 **** --- 184,190 ---- {"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.03/src/backend/parser/parse_expr.c *** pgsql.02/src/backend/parser/parse_expr.c 2005-06-06 15:29:10.000000000 +0200 --- pgsql.03/src/backend/parser/parse_expr.c 2005-06-07 23:23:22.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; *************** *** 1230,1235 **** --- 1235,1291 ---- } static Node * + transformVarargExpr(ParseState *pstate, VarargExpr *v) + { + VarargExpr *newva = makeNode(VarargExpr); + List *newargs = NIL; + List *newcoercedargs = NIL; + List *typeids = NIL; + ListCell *args; + + newva->type = v->type; + + switch (v->type) + { + 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) { const char *clausename; *************** *** 1503,1508 **** --- 1559,1567 ---- 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 **** --- 1696,1726 ---- 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; + + typmod = exprTypmod((Node *) linitial(vexpr->args)); + + foreach(arg, vexpr->args) + { + Node *e = (Node *) lfirst(arg); + + 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.03/src/backend/parser/parse_target.c *** pgsql.02/src/backend/parser/parse_target.c 2005-06-06 15:29:10.000000000 +0200 --- pgsql.03/src/backend/parser/parse_target.c 2005-06-07 23:26:35.000000000 +0200 *************** *** 1123,1128 **** --- 1123,1139 ---- /* 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; + } + default: break; } diff -c -r --new-file pgsql.02/src/backend/utils/adt/ruleutils.c pgsql.03/src/backend/utils/adt/ruleutils.c *** pgsql.02/src/backend/utils/adt/ruleutils.c 2005-06-06 15:29:19.000000000 +0200 --- pgsql.03/src/backend/utils/adt/ruleutils.c 2005-06-07 23:26:56.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,3517 ---- } 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; + } + 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.03/src/include/nodes/execnodes.h *** pgsql.02/src/include/nodes/execnodes.h 2005-06-06 15:29:42.000000000 +0200 --- pgsql.03/src/include/nodes/execnodes.h 2005-06-07 23:31:20.000000000 +0200 *************** *** 672,677 **** --- 672,691 ---- } CoalesceExprState; /* ---------------- + * VarargExprState node + * ---------------- + */ + typedef struct VarargExprState + { + ExprState xprstate; + VarargExprType type; + Oid varargtype; /* type of arguments and result */ + List *args; /* the arguments */ + } VarargExprState; + + + + /* ---------------- * CoerceToDomainState node * ---------------- */ diff -c -r --new-file pgsql.02/src/include/nodes/nodes.h pgsql.03/src/include/nodes/nodes.h *** pgsql.02/src/include/nodes/nodes.h 2005-06-06 15:29:42.000000000 +0200 --- pgsql.03/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.03/src/include/nodes/primnodes.h *** pgsql.02/src/include/nodes/primnodes.h 2005-06-06 15:29:42.000000000 +0200 --- pgsql.03/src/include/nodes/primnodes.h 2005-06-07 23:48:06.000000000 +0200 *************** *** 657,662 **** --- 657,681 ---- List *args; /* the arguments */ } CoalesceExpr; + + /* + * VarargExpr - a GREATEST, LEAST expression + */ + + typedef enum VarargExprType + { + IS_GREATEST, + IS_LEAST, + } VarargExprType; + + typedef struct VarargExpr + { + Expr xpr; + Oid varargtype; + VarargExprType type; + List *args; + } VarargExpr; + /* * NullIfExpr - a NULLIF expression * diff -c -r --new-file pgsql.02/src/test/regress/expected/oracle.out pgsql.03/src/test/regress/expected/oracle.out *** pgsql.02/src/test/regress/expected/oracle.out 1970-01-01 01:00:00.000000000 +0100 --- pgsql.03/src/test/regress/expected/oracle.out 2005-06-07 23:58:45.000000000 +0200 *************** *** 0 **** --- 1,41 ---- + 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) diff -c -r --new-file pgsql.02/src/test/regress/sql/oracle.sql pgsql.03/src/test/regress/sql/oracle.sql *** pgsql.02/src/test/regress/sql/oracle.sql 1970-01-01 01:00:00.000000000 +0100 --- pgsql.03/src/test/regress/sql/oracle.sql 2005-06-07 23:59:01.000000000 +0200 *************** *** 0 **** --- 1,7 ---- + 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);