Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit943b396

Browse files
committed
Add Oracle-compatible GREATEST and LEAST functions. Pavel Stehule
1 parentd395aec commit943b396

File tree

17 files changed

+409
-36
lines changed

17 files changed

+409
-36
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 34 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.259 2005/06/24 20:53:29 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.260 2005/06/26 22:05:35 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -6901,6 +6901,39 @@ SELECT NULLIF(value, '(none)') ...
69016901

69026902
</sect2>
69036903

6904+
<sect2>
6905+
<title><literal>GREATEST</literal> and <literal>LEAST</literal></title>
6906+
6907+
<indexterm>
6908+
<primary>GREATEST</primary>
6909+
</indexterm>
6910+
<indexterm>
6911+
<primary>LEAST</primary>
6912+
</indexterm>
6913+
6914+
<synopsis>
6915+
<function>GREATEST</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
6916+
</synopsis>
6917+
<synopsis>
6918+
<function>LEAST</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
6919+
</synopsis>
6920+
6921+
<para>
6922+
The <function>GREATEST</> and <function>LEAST</> functions select the
6923+
largest or smallest value from a list of any number of expressions.
6924+
The expressions must all be convertible to a common data type, which
6925+
will be the type of the result
6926+
(see <xref linkend="typeconv-union-case"> for details). NULL values
6927+
in the list are ignored. The result will be NULL only if all the
6928+
expressions evaluate to NULL.
6929+
</para>
6930+
6931+
<para>
6932+
Note that <function>GREATEST</> and <function>LEAST</> are not in
6933+
the SQL standard, but are a common extension.
6934+
</para>
6935+
</sect2>
6936+
69046937
</sect1>
69056938

69066939

‎doc/src/sgml/typeconv.sgml

Lines changed: 19 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/typeconv.sgml,v 1.43 2004/12/23 23:07:38 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/typeconv.sgml,v 1.44 2005/06/26 22:05:36 tgl Exp $
33
-->
44

55
<chapter Id="typeconv">
@@ -120,7 +120,7 @@ with, and perhaps converted to, the types of the target columns.
120120
</varlistentry>
121121
<varlistentry>
122122
<term>
123-
<literal>UNION</literal>, <literal>CASE</literal>, and<literal>ARRAY</literal> constructs
123+
<literal>UNION</literal>, <literal>CASE</literal>, andrelated constructs
124124
</term>
125125
<listitem>
126126
<para>
@@ -129,7 +129,8 @@ must appear in a single set of columns, the types of the results of each
129129
<command>SELECT</> clause must be matched up and converted to a uniform set.
130130
Similarly, the result expressions of a <literal>CASE</> construct must be
131131
converted to a common type so that the <literal>CASE</> expression as a whole
132-
has a known output type. The same holds for <literal>ARRAY</> constructs.
132+
has a known output type. The same holds for <literal>ARRAY</> constructs,
133+
and for the <function>GREATEST</> and <function>LEAST</> functions.
133134
</para>
134135
</listitem>
135136
</varlistentry>
@@ -782,7 +783,7 @@ padding spaces.
782783
</sect1>
783784

784785
<sect1 id="typeconv-union-case">
785-
<title><literal>UNION</literal>, <literal>CASE</literal>, and<literal>ARRAY</literal> Constructs</title>
786+
<title><literal>UNION</literal>, <literal>CASE</literal>, andRelated Constructs</title>
786787

787788
<indexterm zone="typeconv-union-case">
788789
<primary>UNION</primary>
@@ -799,20 +800,31 @@ padding spaces.
799800
<secondary>determination of result type</secondary>
800801
</indexterm>
801802

803+
<indexterm zone="typeconv-union-case">
804+
<primary>GREATEST</primary>
805+
<secondary>determination of result type</secondary>
806+
</indexterm>
807+
808+
<indexterm zone="typeconv-union-case">
809+
<primary>LEAST</primary>
810+
<secondary>determination of result type</secondary>
811+
</indexterm>
812+
802813
<para>
803814
SQL <literal>UNION</> constructs must match up possibly dissimilar
804815
types to become a single result set. The resolution algorithm is
805816
applied separately to each output column of a union query. The
806817
<literal>INTERSECT</> and <literal>EXCEPT</> constructs resolve
807818
dissimilar types in the same way as <literal>UNION</>. The
808-
<literal>CASE</> and <literal>ARRAY</> constructs use the identical
819+
<literal>CASE</>, <literal>ARRAY</>, <function>GREATEST</> and
820+
<function>LEAST</> constructs use the identical
809821
algorithm to match up their component expressions and select a result
810822
data type.
811823
</para>
812824

813825
<procedure>
814-
<title><literal>UNION</literal>, <literal>CASE</literal>, and
815-
<literal>ARRAY</literal> Type Resolution</title>
826+
<title>Type Resolution for<literal>UNION</literal>, <literal>CASE</literal>,
827+
and Related Constructs</title>
816828

817829
<step performance="required">
818830
<para>

‎src/backend/executor/execQual.c

Lines changed: 91 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.179 2005/05/12 20:41:56 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.180 2005/06/26 22:05:36 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -105,6 +105,9 @@ static Datum ExecEvalRow(RowExprState *rstate,
105105
staticDatumExecEvalCoalesce(CoalesceExprState*coalesceExpr,
106106
ExprContext*econtext,
107107
bool*isNull,ExprDoneCond*isDone);
108+
staticDatumExecEvalMinMax(MinMaxExprState*minmaxExpr,
109+
ExprContext*econtext,
110+
bool*isNull,ExprDoneCond*isDone);
108111
staticDatumExecEvalNullIf(FuncExprState*nullIfExpr,
109112
ExprContext*econtext,
110113
bool*isNull,ExprDoneCond*isDone);
@@ -2247,6 +2250,63 @@ ExecEvalCoalesce(CoalesceExprState *coalesceExpr, ExprContext *econtext,
22472250
return (Datum)0;
22482251
}
22492252

2253+
/* ----------------------------------------------------------------
2254+
*ExecEvalMinMax
2255+
* ----------------------------------------------------------------
2256+
*/
2257+
staticDatum
2258+
ExecEvalMinMax(MinMaxExprState*minmaxExpr,ExprContext*econtext,
2259+
bool*isNull,ExprDoneCond*isDone)
2260+
{
2261+
Datumresult= (Datum)0;
2262+
MinMaxOpop= ((MinMaxExpr*)minmaxExpr->xprstate.expr)->op;
2263+
FunctionCallInfoDatalocfcinfo;
2264+
ListCell*arg;
2265+
2266+
if (isDone)
2267+
*isDone=ExprSingleResult;
2268+
*isNull= true;/* until we get a result */
2269+
2270+
InitFunctionCallInfoData(locfcinfo,&minmaxExpr->cfunc,2,NULL,NULL);
2271+
locfcinfo.argnull[0]= false;
2272+
locfcinfo.argnull[1]= false;
2273+
2274+
foreach(arg,minmaxExpr->args)
2275+
{
2276+
ExprState*e= (ExprState*)lfirst(arg);
2277+
Datumvalue;
2278+
boolvalueIsNull;
2279+
int32cmpresult;
2280+
2281+
value=ExecEvalExpr(e,econtext,&valueIsNull,NULL);
2282+
if (valueIsNull)
2283+
continue;/* ignore NULL inputs */
2284+
2285+
if (*isNull)
2286+
{
2287+
/* first nonnull input, adopt value */
2288+
result=value;
2289+
*isNull= false;
2290+
}
2291+
else
2292+
{
2293+
/* apply comparison function */
2294+
locfcinfo.arg[0]=result;
2295+
locfcinfo.arg[1]=value;
2296+
locfcinfo.isnull= false;
2297+
cmpresult=DatumGetInt32(FunctionCallInvoke(&locfcinfo));
2298+
if (locfcinfo.isnull)/* probably should not happen */
2299+
continue;
2300+
if (cmpresult>0&&op==IS_LEAST)
2301+
result=value;
2302+
elseif (cmpresult<0&&op==IS_GREATEST)
2303+
result=value;
2304+
}
2305+
}
2306+
2307+
returnresult;
2308+
}
2309+
22502310
/* ----------------------------------------------------------------
22512311
*ExecEvalNullIf
22522312
*
@@ -3206,6 +3266,36 @@ ExecInitExpr(Expr *node, PlanState *parent)
32063266
state= (ExprState*)cstate;
32073267
}
32083268
break;
3269+
caseT_MinMaxExpr:
3270+
{
3271+
MinMaxExpr*minmaxexpr= (MinMaxExpr*)node;
3272+
MinMaxExprState*mstate=makeNode(MinMaxExprState);
3273+
List*outlist=NIL;
3274+
ListCell*l;
3275+
TypeCacheEntry*typentry;
3276+
3277+
mstate->xprstate.evalfunc= (ExprStateEvalFunc)ExecEvalMinMax;
3278+
foreach(l,minmaxexpr->args)
3279+
{
3280+
Expr*e= (Expr*)lfirst(l);
3281+
ExprState*estate;
3282+
3283+
estate=ExecInitExpr(e,parent);
3284+
outlist=lappend(outlist,estate);
3285+
}
3286+
mstate->args=outlist;
3287+
/* Look up the btree comparison function for the datatype */
3288+
typentry=lookup_type_cache(minmaxexpr->minmaxtype,
3289+
TYPECACHE_CMP_PROC);
3290+
if (!OidIsValid(typentry->cmp_proc))
3291+
ereport(ERROR,
3292+
(errcode(ERRCODE_UNDEFINED_FUNCTION),
3293+
errmsg("could not identify a comparison function for type %s",
3294+
format_type_be(minmaxexpr->minmaxtype))));
3295+
fmgr_info(typentry->cmp_proc,&(mstate->cfunc));
3296+
state= (ExprState*)mstate;
3297+
}
3298+
break;
32093299
caseT_NullIfExpr:
32103300
{
32113301
NullIfExpr*nullifexpr= (NullIfExpr*)node;

‎src/backend/nodes/copyfuncs.c

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.308 2005/06/22 21:14:29 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.309 2005/06/26 22:05:37 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1047,6 +1047,21 @@ _copyCoalesceExpr(CoalesceExpr *from)
10471047
returnnewnode;
10481048
}
10491049

1050+
/*
1051+
* _copyMinMaxExpr
1052+
*/
1053+
staticMinMaxExpr*
1054+
_copyMinMaxExpr(MinMaxExpr*from)
1055+
{
1056+
MinMaxExpr*newnode=makeNode(MinMaxExpr);
1057+
1058+
COPY_SCALAR_FIELD(minmaxtype);
1059+
COPY_SCALAR_FIELD(op);
1060+
COPY_NODE_FIELD(args);
1061+
1062+
returnnewnode;
1063+
}
1064+
10501065
/*
10511066
* _copyNullIfExpr (same as OpExpr)
10521067
*/
@@ -2805,6 +2820,9 @@ copyObject(void *from)
28052820
caseT_CoalesceExpr:
28062821
retval=_copyCoalesceExpr(from);
28072822
break;
2823+
caseT_MinMaxExpr:
2824+
retval=_copyMinMaxExpr(from);
2825+
break;
28082826
caseT_NullIfExpr:
28092827
retval=_copyNullIfExpr(from);
28102828
break;

‎src/backend/nodes/equalfuncs.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.245 2005/06/22 21:14:29 tgl Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.246 2005/06/26 22:05:37 tgl Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -450,6 +450,16 @@ _equalCoalesceExpr(CoalesceExpr *a, CoalesceExpr *b)
450450
return true;
451451
}
452452

453+
staticbool
454+
_equalMinMaxExpr(MinMaxExpr*a,MinMaxExpr*b)
455+
{
456+
COMPARE_SCALAR_FIELD(minmaxtype);
457+
COMPARE_SCALAR_FIELD(op);
458+
COMPARE_NODE_FIELD(args);
459+
460+
return true;
461+
}
462+
453463
staticbool
454464
_equalNullIfExpr(NullIfExpr*a,NullIfExpr*b)
455465
{
@@ -1868,6 +1878,9 @@ equal(void *a, void *b)
18681878
caseT_CoalesceExpr:
18691879
retval=_equalCoalesceExpr(a,b);
18701880
break;
1881+
caseT_MinMaxExpr:
1882+
retval=_equalMinMaxExpr(a,b);
1883+
break;
18711884
caseT_NullIfExpr:
18721885
retval=_equalNullIfExpr(a,b);
18731886
break;

‎src/backend/nodes/outfuncs.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.255 2005/06/09 04:18:58 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.256 2005/06/26 22:05:37 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -864,6 +864,16 @@ _outCoalesceExpr(StringInfo str, CoalesceExpr *node)
864864
WRITE_NODE_FIELD(args);
865865
}
866866

867+
staticvoid
868+
_outMinMaxExpr(StringInfostr,MinMaxExpr*node)
869+
{
870+
WRITE_NODE_TYPE("MINMAX");
871+
872+
WRITE_OID_FIELD(minmaxtype);
873+
WRITE_ENUM_FIELD(op,MinMaxOp);
874+
WRITE_NODE_FIELD(args);
875+
}
876+
867877
staticvoid
868878
_outNullIfExpr(StringInfostr,NullIfExpr*node)
869879
{
@@ -1896,6 +1906,9 @@ _outNode(StringInfo str, void *obj)
18961906
caseT_CoalesceExpr:
18971907
_outCoalesceExpr(str,obj);
18981908
break;
1909+
caseT_MinMaxExpr:
1910+
_outMinMaxExpr(str,obj);
1911+
break;
18991912
caseT_NullIfExpr:
19001913
_outNullIfExpr(str,obj);
19011914
break;

‎src/backend/nodes/readfuncs.c

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.178 2005/06/05 22:32:54 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.179 2005/06/26 22:05:37 tgl Exp $
1212
*
1313
* NOTES
1414
* Path and Plan nodes do not have any readfuncs support, because we
@@ -658,6 +658,21 @@ _readCoalesceExpr(void)
658658
READ_DONE();
659659
}
660660

661+
/*
662+
* _readMinMaxExpr
663+
*/
664+
staticMinMaxExpr*
665+
_readMinMaxExpr(void)
666+
{
667+
READ_LOCALS(MinMaxExpr);
668+
669+
READ_OID_FIELD(minmaxtype);
670+
READ_ENUM_FIELD(op,MinMaxOp);
671+
READ_NODE_FIELD(args);
672+
673+
READ_DONE();
674+
}
675+
661676
/*
662677
* _readNullIfExpr
663678
*/
@@ -982,6 +997,8 @@ parseNodeString(void)
982997
return_value=_readRowExpr();
983998
elseif (MATCH("COALESCE",8))
984999
return_value=_readCoalesceExpr();
1000+
elseif (MATCH("MINMAX",6))
1001+
return_value=_readMinMaxExpr();
9851002
elseif (MATCH("NULLIFEXPR",10))
9861003
return_value=_readNullIfExpr();
9871004
elseif (MATCH("NULLTEST",8))

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp