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

Commit0079547

Browse files
author
Neil Conway
committed
Implement the width_bucket() function, per SQL2003. This commit only adds
a variant of the function for the 'numeric' datatype; it would be possibleto add additional variants for other datatypes, but I haven't done so yet.This commit includes regression tests and minimal documentation; if wewant developers to actually use this function in applications, we'llprobably need to document what it does more fully.
1 parent19a495c commit0079547

File tree

10 files changed

+272
-23
lines changed

10 files changed

+272
-23
lines changed

‎doc/src/sgml/errcodes.sgml‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.4 2004/05/1418:04:02 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.5 2004/05/1421:42:27 neilc Exp $ -->
22

33
<appendix id="errcodes-appendix">
44
<title><productname>PostgreSQL</productname> Error Codes</title>
@@ -310,6 +310,11 @@
310310
<entry>INTERVAL FIELD OVERFLOW</entry>
311311
</row>
312312

313+
<row>
314+
<entry><literal>2201G</literal></entry>
315+
<entry>INVALID ARGUMENT FOR WIDTH BUCKET FUNCTION</entry>
316+
</row>
317+
313318
<row>
314319
<entry><literal>22018</literal></entry>
315320
<entry>INVALID CHARACTER VALUE FOR CAST</entry>

‎doc/src/sgml/func.sgml‎

Lines changed: 11 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.201 2004/05/10 22:44:42 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.202 2004/05/14 21:42:27 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -751,6 +751,16 @@ PostgreSQL documentation
751751
<entry><literal>42.43</literal></entry>
752752
</row>
753753

754+
<row>
755+
<entry><literal><function>width_bucket</function>(<parameter>op</parameter> <type>numeric</type>, <parameter>b1</parameter> <type>numeric</type>, <parameter>b2</parameter> <type>numeric</type>, <parameter>count</parameter> <type>integer</type>)</literal></entry>
756+
<entry><type>integer</type></entry>
757+
<entry>return the bucket to which <parameter>operand</> would
758+
be assigned in an equidepth histogram with <parameter>count</>
759+
buckets, an upper bound of <parameter>b1</>, and a lower bound
760+
of <parameter>b2</></entry>
761+
<entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>
762+
<entry><literal>3</literal></entry>
763+
</row>
754764
</tbody>
755765
</tgroup>
756766
</table>

‎doc/src/sgml/xfunc.sgml‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.82 2004/05/10 22:44:43 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.83 2004/05/14 21:42:27 neilc Exp $
33
-->
44

55
<sect1 id="xfunc">
@@ -56,20 +56,20 @@ $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.82 2004/05/10 22:44:43 tgl Exp $
5656
</para>
5757

5858
<para>
59-
It's easiest to define <acronym>SQL</acronym>
59+
It's easiest to define <acronym>SQL</acronym>
6060
functions, so we'll start by discussing those.
6161
Most of the concepts presented for <acronym>SQL</acronym> functions
6262
will carry over to the other types of functions.
6363
</para>
6464

6565
<para>
6666
Throughout this chapter, it can be useful to look at the reference
67-
page of the <command>CREATE FUNCTION</command> command to
68-
understand the examples better.
69-
Some examples from this chapter
70-
can be found in<filename>funcs.sql</filename>
71-
and <filename>funcs.c</filename>in the <filename>src/tutorial</>
72-
directory in the <productname>PostgreSQL</productname> sourcedistribution.
67+
page of the <xref linkend="sql-createfunction"> command to
68+
understand the examples better. Some examples from this chapter
69+
can be found in <filename>funcs.sql</filename> and
70+
<filename>funcs.c</filename>in the <filename>src/tutorial</>
71+
directoryin the <productname>PostgreSQL</productname> source
72+
distribution.
7373
</para>
7474
</sect1>
7575

‎src/backend/utils/adt/numeric.c‎

Lines changed: 141 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Copyright (c) 1998-2003, PostgreSQL Global Development Group
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.73 2004/05/07 00:24:58 tgl Exp $
17+
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.74 2004/05/14 21:42:28 neilc Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -252,6 +252,7 @@ static Numeric make_result(NumericVar *var);
252252

253253
staticvoidapply_typmod(NumericVar*var,int32typmod);
254254

255+
staticint32numericvar_to_int4(NumericVar*var);
255256
staticboolnumericvar_to_int8(NumericVar*var,int64*result);
256257
staticvoidint8_to_numericvar(int64val,NumericVar*var);
257258
staticdoublenumeric_to_double_no_overflow(Numericnum);
@@ -285,6 +286,8 @@ static void sub_abs(NumericVar *var1, NumericVar *var2, NumericVar *result);
285286
staticvoidround_var(NumericVar*var,intrscale);
286287
staticvoidtrunc_var(NumericVar*var,intrscale);
287288
staticvoidstrip_var(NumericVar*var);
289+
staticvoidcompute_bucket(Numericoperand,Numericbound1,Numericbound2,
290+
NumericVar*count_var,NumericVar*result_var);
288291

289292

290293
/* ----------------------------------------------------------------------
@@ -803,6 +806,125 @@ numeric_floor(PG_FUNCTION_ARGS)
803806
PG_RETURN_NUMERIC(res);
804807
}
805808

809+
/*
810+
* width_bucket_numeric() -
811+
*
812+
* 'bound1' and 'bound2' are the lower and upper bounds of the
813+
* histogram's range, respectively. 'count' is the number of buckets
814+
* in the histogram. width_bucket() returns an integer indicating the
815+
* bucket number that 'operand' belongs in for an equiwidth histogram
816+
* with the specified characteristics. An operand smaller than the
817+
* lower bound is assigned to bucket 0. An operand greater than the
818+
* upper bound is assigned to an additional bucket (with number
819+
* count+1).
820+
*/
821+
Datum
822+
width_bucket_numeric(PG_FUNCTION_ARGS)
823+
{
824+
Numericoperand=PG_GETARG_NUMERIC(0);
825+
Numericbound1=PG_GETARG_NUMERIC(1);
826+
Numericbound2=PG_GETARG_NUMERIC(2);
827+
int32count=PG_GETARG_INT32(3);
828+
NumericVarcount_var;
829+
NumericVarresult_var;
830+
int32result;
831+
832+
if (count <=0)
833+
ereport(ERROR,
834+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
835+
errmsg("count must be greater than zero")));
836+
837+
init_var(&result_var);
838+
init_var(&count_var);
839+
840+
/* Convert 'count' to a numeric, for ease of use later */
841+
int8_to_numericvar((int64)count,&count_var);
842+
843+
switch (cmp_numerics(bound1,bound2))
844+
{
845+
case0:
846+
ereport(ERROR,
847+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
848+
errmsg("lower bound cannot equal upper bound")));
849+
850+
/* bound1 < bound2 */
851+
case-1:
852+
if (cmp_numerics(operand,bound1)<0)
853+
set_var_from_var(&const_zero,&result_var);
854+
elseif (cmp_numerics(operand,bound2) >=0)
855+
add_var(&count_var,&const_one,&result_var);
856+
else
857+
compute_bucket(operand,bound1,bound2,
858+
&count_var,&result_var);
859+
break;
860+
861+
/* bound1 > bound2 */
862+
case1:
863+
if (cmp_numerics(operand,bound1)>0)
864+
set_var_from_var(&const_zero,&result_var);
865+
elseif (cmp_numerics(operand,bound2) <=0)
866+
add_var(&count_var,&const_one,&result_var);
867+
else
868+
compute_bucket(operand,bound1,bound2,
869+
&count_var,&result_var);
870+
break;
871+
}
872+
873+
result=numericvar_to_int4(&result_var);
874+
875+
free_var(&count_var);
876+
free_var(&result_var);
877+
878+
PG_RETURN_INT32(result);
879+
}
880+
881+
/*
882+
* compute_bucket() -
883+
*
884+
* If 'operand' is not outside the bucket range, determine the correct
885+
* bucket for it to go. The calculations performed by this function
886+
* are derived directly from the SQL2003 spec.
887+
*/
888+
staticvoid
889+
compute_bucket(Numericoperand,Numericbound1,Numericbound2,
890+
NumericVar*count_var,NumericVar*result_var)
891+
{
892+
NumericVarbound1_var;
893+
NumericVarbound2_var;
894+
NumericVaroperand_var;
895+
896+
init_var(&bound1_var);
897+
init_var(&bound2_var);
898+
init_var(&operand_var);
899+
900+
set_var_from_num(bound1,&bound1_var);
901+
set_var_from_num(bound2,&bound2_var);
902+
set_var_from_num(operand,&operand_var);
903+
904+
if (cmp_var(&bound1_var,&bound2_var)<0)
905+
{
906+
sub_var(&operand_var,&bound1_var,&operand_var);
907+
sub_var(&bound2_var,&bound1_var,&bound2_var);
908+
div_var(&operand_var,&bound2_var,result_var,
909+
select_div_scale(&operand_var,&bound2_var));
910+
}
911+
else
912+
{
913+
sub_var(&bound1_var,&operand_var,&operand_var);
914+
sub_var(&bound1_var,&bound2_var,&bound1_var);
915+
div_var(&operand_var,&bound1_var,result_var,
916+
select_div_scale(&operand_var,&bound1_var));
917+
}
918+
919+
mul_var(result_var,count_var,result_var,
920+
result_var->dscale+count_var->dscale);
921+
add_var(result_var,&const_one,result_var);
922+
floor_var(result_var,result_var);
923+
924+
free_var(&bound1_var);
925+
free_var(&bound2_var);
926+
free_var(&operand_var);
927+
}
806928

807929
/* ----------------------------------------------------------------------
808930
*
@@ -1612,7 +1734,6 @@ numeric_int4(PG_FUNCTION_ARGS)
16121734
{
16131735
Numericnum=PG_GETARG_NUMERIC(0);
16141736
NumericVarx;
1615-
int64val;
16161737
int32result;
16171738

16181739
/* XXX would it be better to return NULL? */
@@ -1621,17 +1742,30 @@ numeric_int4(PG_FUNCTION_ARGS)
16211742
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
16221743
errmsg("cannot convert NaN to integer")));
16231744

1624-
/* Convert to variable format and thence toint8 */
1745+
/* Convert to variable format, then convert toint4 */
16251746
init_var(&x);
16261747
set_var_from_num(num,&x);
1748+
result=numericvar_to_int4(&x);
1749+
free_var(&x);
1750+
PG_RETURN_INT32(result);
1751+
}
16271752

1628-
if (!numericvar_to_int8(&x,&val))
1753+
/*
1754+
* Given a NumericVar, convert it to an int32. If the NumericVar
1755+
* exceeds the range of an int32, raise the appropriate error via
1756+
* ereport(). The input NumericVar is *not* free'd.
1757+
*/
1758+
staticint32
1759+
numericvar_to_int4(NumericVar*var)
1760+
{
1761+
int32result;
1762+
int64val;
1763+
1764+
if (!numericvar_to_int8(var,&val))
16291765
ereport(ERROR,
16301766
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
16311767
errmsg("integer out of range")));
16321768

1633-
free_var(&x);
1634-
16351769
/* Down-convert to int4 */
16361770
result= (int32)val;
16371771

@@ -1641,10 +1775,9 @@ numeric_int4(PG_FUNCTION_ARGS)
16411775
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
16421776
errmsg("integer out of range")));
16431777

1644-
PG_RETURN_INT32(result);
1778+
returnresult;
16451779
}
16461780

1647-
16481781
Datum
16491782
int8_numeric(PG_FUNCTION_ARGS)
16501783
{

‎src/include/catalog/catversion.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.229 2004/05/10 22:44:49 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.230 2004/05/14 21:42:28 neilc Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200405101
56+
#defineCATALOG_VERSION_NO200405141
5757

5858
#endif

‎src/include/catalog/pg_proc.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.328 2004/05/07 16:57:16 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.329 2004/05/14 21:42:28 neilc Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2508,6 +2508,8 @@ DATA(insert OID = 1745 ( float4PGNSP PGUID 12 f f t f i 1 700 "1700" _null_
25082508
DESCR("(internal)");
25092509
DATA(insertOID=1746 (float8PGNSPPGUID12fftfi1701"1700"_null_numeric_float8-_null_ ));
25102510
DESCR("(internal)");
2511+
DATA(insertOID=2170 (width_bucketPGNSPPGUID12fftfi423"1700 1700 1700 23"_null_width_bucket_numeric-_null_ ));
2512+
DESCR("bucket number of operand in equidepth histogram");
25112513

25122514
DATA(insertOID=1747 (time_pl_intervalPGNSPPGUID12fftfi21083"1083 1186"_null_time_pl_interval-_null_ ));
25132515
DESCR("plus");

‎src/include/utils/builtins.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.237 2004/05/05 04:48:47 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.238 2004/05/14 21:42:30 neilc Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -758,6 +758,7 @@ extern Datum int8_sum(PG_FUNCTION_ARGS);
758758
externDatumint2_avg_accum(PG_FUNCTION_ARGS);
759759
externDatumint4_avg_accum(PG_FUNCTION_ARGS);
760760
externDatumint8_avg(PG_FUNCTION_ARGS);
761+
externDatumwidth_bucket_numeric(PG_FUNCTION_ARGS);
761762

762763
/* ri_triggers.c */
763764
externDatumRI_FKey_check_ins(PG_FUNCTION_ARGS);

‎src/include/utils/errcodes.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
* Copyright (c) 2003, PostgreSQL Global Development Group
1313
*
14-
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.9 2004/05/1418:04:02 neilc Exp $
14+
* $PostgreSQL: pgsql/src/include/utils/errcodes.h,v 1.10 2004/05/1421:42:30 neilc Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -116,6 +116,7 @@
116116
#defineERRCODE_ESCAPE_CHARACTER_CONFLICTMAKE_SQLSTATE('2','2', '0','0','B')
117117
#defineERRCODE_INDICATOR_OVERFLOWMAKE_SQLSTATE('2','2', '0','2','2')
118118
#defineERRCODE_INTERVAL_FIELD_OVERFLOWMAKE_SQLSTATE('2','2', '0','1','5')
119+
#defineERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTIONMAKE_SQLSTATE('2','2', '0', '1', 'G')
119120
#defineERRCODE_INVALID_CHARACTER_VALUE_FOR_CASTMAKE_SQLSTATE('2','2', '0','1','8')
120121
#defineERRCODE_INVALID_DATETIME_FORMATMAKE_SQLSTATE('2','2', '0','0','7')
121122
#defineERRCODE_INVALID_ESCAPE_CHARACTERMAKE_SQLSTATE('2','2', '0','1','9')

0 commit comments

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