Defining a "tinyint" data type - one byte unsigned

Hi all,

Attached is a patch to implement "tinyint". This is a one byte unsigned
integer type. It is required for porting applications built for
sql-server that work in binary mode.

This is my first attempt at hacking postgresql proper, so please be
patient with me. I have run it on 7.5 locally, and it passes all tests.
If there was anything else I should have done before submitting it,
please let me know.

I chose "10" as the oid for the new type, as it seems to sort well in
the types sizes list (21 is int2, 23 is int4). I took the OID for the
conversion functions from the top to avoid collisions.

Also, I'm interested in knowning whether there is any chance of
introducing anything of this like into 7.4, or whether I should create
that as an extrenal type there.

        Thanks,

                 Shachar

--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/


Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/doc/src/sgml/datatype.sgml,v
retrieving revision 1.143
diff -c -r1.143 datatype.sgml
*** a/doc/src/sgml/datatype.sgml    9 Mar 2004 16:57:46 -0000    1.143
--- b/doc/src/sgml/datatype.sgml    10 Mar 2004 10:46:57 -0000
***************
*** 236,241 ****
--- 236,247 ----
         <entry><type>timestamptz</type></entry>
         <entry>date and time, including time zone</entry>
        </row>
+
+       <row>
+        <entry><type>tinyint</type></entry>
+        <entry></entry>
+        <entry>unsigned one-byte integer</entry>
+       </row>
       </tbody>
      </tgroup>
     </table>
***************
*** 304,309 ****
--- 310,321 ----

        <tbody>
         <row>
+         <entry><type>tinyint</></entry>
+         <entry>1 byte</entry>
+         <entry>tiny unsigned integer</entry>
+         <entry>0 to 255</entry>
+        </row>
+        <row>
          <entry><type>smallint</></entry>
          <entry>2 bytes</entry>
          <entry>small-range integer</entry>
***************
*** 381,386 ****
--- 393,402 ----
      </indexterm>

      <indexterm zone="datatype-int">
+      <primary>tinyint</primary>
+     </indexterm>
+
+     <indexterm zone="datatype-int">
       <primary>smallint</primary>
      </indexterm>

***************
*** 404,413 ****
      </indexterm>

      <para>
!      The types <type>smallint</type>, <type>integer</type>, and
!      <type>bigint</type> store whole numbers, that is, numbers without
!      fractional components, of various ranges.  Attempts to store
!      values outside of the allowed range will result in an error.
      </para>

      <para>
--- 420,430 ----
      </indexterm>

      <para>
!      The types <type>tinyint</type>, <type>smallint</type>,
!      <type>integer</type> and <type>bigint</type> store whole
!      numbers, that is, numbers without fractional components, of
!      various ranges.  Attempts to store values outside of the
!      allowed range will result in an error.
      </para>

      <para>
***************
*** 431,440 ****
      <para>
       <acronym>SQL</acronym> only specifies the integer types
       <type>integer</type> (or <type>int</type>) and
!      <type>smallint</type>.  The type <type>bigint</type>, and the
!      type names <type>int2</type>, <type>int4</type>, and
!      <type>int8</type> are extensions, which are shared with various
!      other <acronym>SQL</acronym> database systems.
      </para>

     </sect2>
--- 448,457 ----
      <para>
       <acronym>SQL</acronym> only specifies the integer types
       <type>integer</type> (or <type>int</type>) and
!      <type>smallint</type>.  The types <type>tinyint</type>,
!      <type>bigint</type>, and the type names <type>int2</type>,
!      <type>int4</type>, and <type>int8</type> are extensions, which are
!      shared with various other <acronym>SQL</acronym> database systems.
      </para>

     </sect2>
Index: src/backend/utils/adt/int.c
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/src/backend/utils/adt/int.c,v
retrieving revision 1.60
diff -c -r1.60 int.c
*** a/src/backend/utils/adt/int.c    3 Feb 2004 08:29:56 -0000    1.60
--- b/src/backend/utils/adt/int.c    10 Mar 2004 10:40:33 -0000
***************
*** 15,25 ****
  /*
   * OLD COMMENTS
   *        I/O routines:
   *         int2in, int2out, int2recv, int2send
   *         int4in, int4out, int4recv, int4send
   *         int2vectorin, int2vectorout, int2vectorrecv, int2vectorsend
   *        Conversion routines:
!  *         itoi, int2_text, int4_text
   *        Boolean operators:
   *         inteq, intne, intlt, intle, intgt, intge
   *        Arithmetic operators:
--- 15,26 ----
  /*
   * OLD COMMENTS
   *        I/O routines:
+  *         tinyintin, tinyintout, tinyintrecv, tinyintsend
   *         int2in, int2out, int2recv, int2send
   *         int4in, int4out, int4recv, int4send
   *         int2vectorin, int2vectorout, int2vectorrecv, int2vectorsend
   *        Conversion routines:
!  *         itoi, tinyint_text, int2_text, int4_text
   *        Boolean operators:
   *         inteq, intne, intlt, intle, intgt, intge
   *        Arithmetic operators:
***************
*** 38,43 ****
--- 39,48 ----
  #include "libpq/pqformat.h"
  #include "utils/builtins.h"

+ #ifndef TINY_MAX
+ #define TINY_MAX (0xFF)
+ #endif
+
  #ifndef SHRT_MAX
  #define SHRT_MAX (0x7FFF)
  #endif
***************
*** 57,62 ****
--- 62,127 ----
   *****************************************************************************/

  /*
+  *        tinyintin            - converts "num" to unsigned byte
+  */
+ Datum
+ tinyintin(PG_FUNCTION_ARGS)
+ {
+     char    *num = PG_GETARG_CSTRING(0);
+     int32    res=pg_atoi(num, sizeof(int32), '\0');
+
+     /* As pg_atoi is signed, we get the number in a int32 and do our own bounds
+      * checking. This results in an incorrect error if the number is bigger than
+      * 2^31-1, or smaller than -2^31. The error message will say that the number
+      * is too big for 32 bits, which is true but slightly misleading.
+      * The alternative is to rewrite pg_atoi, which doesn't seem worth the trouble.
+      */
+     if( res<0 || res>TINY_MAX )
+         ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+                          errmsg("value \"%s\" is out of range for type tinyint", num)));
+
+     PG_RETURN_UINT8(res);
+ }
+
+ /*
+  *        tinyintout            - converts unsigned byte to "num"
+  */
+ Datum
+ tinyintout(PG_FUNCTION_ARGS)
+ {
+     uint8        arg1 = PG_GETARG_UINT8(0);
+     char       *result = (char *) palloc(4);    /* no sign, 3 digits, '\0' */
+
+     pg_itoa(arg1, result);
+     PG_RETURN_CSTRING(result);
+ }
+
+ /*
+  *        tinyintrecv            - converts external binary format to tinyint
+  */
+ Datum
+ tinyintrecv(PG_FUNCTION_ARGS)
+ {
+     StringInfo    buf = (StringInfo) PG_GETARG_POINTER(0);
+
+     PG_RETURN_UINT8((uint8) pq_getmsgint(buf, sizeof(uint8)));
+ }
+
+ /*
+  *        tinyintsend            - converts tinyint to binary format
+  */
+ Datum
+ tinyintsend(PG_FUNCTION_ARGS)
+ {
+     uint8        arg1 = PG_GETARG_UINT8(0);
+     StringInfoData buf;
+
+     pq_begintypsend(&buf);
+     pq_sendint(&buf, arg1, sizeof(uint8));
+     PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
+ }
+
+ /*
   *        int2in            - converts "num" to short
   */
  Datum
***************
*** 276,281 ****
--- 341,388 ----
   */

  Datum
+ tinytoi2(PG_FUNCTION_ARGS)
+ {
+     uint8        arg1 = PG_GETARG_UINT8(0);
+
+     PG_RETURN_INT16((int16) arg1);
+ }
+
+ Datum
+ tinytoi4(PG_FUNCTION_ARGS)
+ {
+     uint8        arg1 = PG_GETARG_UINT8(0);
+
+     PG_RETURN_INT32((int32) arg1);
+ }
+
+ Datum
+ i2totiny(PG_FUNCTION_ARGS)
+ {
+     int16        arg1 = PG_GETARG_INT16(0);
+
+     if (arg1 < 0 || arg1 > TINY_MAX)
+         ereport(ERROR,
+                 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+                  errmsg("integer out of range")));
+
+     PG_RETURN_UINT8((uint8) arg1);
+ }
+
+ Datum
+ i4totiny(PG_FUNCTION_ARGS)
+ {
+     int32        arg1 = PG_GETARG_INT32(0);
+
+     if (arg1 < 0 || arg1 > TINY_MAX)
+         ereport(ERROR,
+                 (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+                  errmsg("integer out of range")));
+
+     PG_RETURN_UINT8((uint8) arg1);
+ }
+
+ Datum
  i2toi4(PG_FUNCTION_ARGS)
  {
      int16        arg1 = PG_GETARG_INT16(0);
***************
*** 297,302 ****
--- 404,440 ----
  }

  Datum
+ tinyint_text(PG_FUNCTION_ARGS)
+ {
+     uint8        arg1 = PG_GETARG_UINT8(0);
+     text       *result = (text *) palloc(4 + VARHDRSZ); /* no sign,4 digits, '\0' */
+
+     pg_itoa(arg1, VARDATA(result));
+     VARATT_SIZEP(result) = strlen(VARDATA(result)) + VARHDRSZ;
+     PG_RETURN_TEXT_P(result);
+ }
+
+ Datum
+ text_tinyint(PG_FUNCTION_ARGS)
+ {
+     text       *string = PG_GETARG_TEXT_P(0);
+     Datum        result;
+     int            len;
+     char       *str;
+
+     len = VARSIZE(string) - VARHDRSZ;
+
+     str = palloc(len + 1);
+     memcpy(str, VARDATA(string), len);
+     *(str + len) = '\0';
+
+     result = DirectFunctionCall1(tinyintin, CStringGetDatum(str));
+     pfree(str);
+
+     return result;
+ }
+
+ Datum
  int2_text(PG_FUNCTION_ARGS)
  {
      int16        arg1 = PG_GETARG_INT16(0);
Index: src/include/fmgr.h
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/src/include/fmgr.h,v
retrieving revision 1.33
diff -c -r1.33 fmgr.h
*** a/src/include/fmgr.h    19 Jan 2004 02:06:42 -0000    1.33
--- b/src/include/fmgr.h    9 Mar 2004 10:55:00 -0000
***************
*** 173,178 ****
--- 173,179 ----
  #define PG_GETARG_UINT32(n)  DatumGetUInt32(PG_GETARG_DATUM(n))
  #define PG_GETARG_INT16(n)     DatumGetInt16(PG_GETARG_DATUM(n))
  #define PG_GETARG_UINT16(n)  DatumGetUInt16(PG_GETARG_DATUM(n))
+ #define PG_GETARG_UINT8(n)     DatumGetUInt8(PG_GETARG_DATUM(n))
  #define PG_GETARG_CHAR(n)     DatumGetChar(PG_GETARG_DATUM(n))
  #define PG_GETARG_BOOL(n)     DatumGetBool(PG_GETARG_DATUM(n))
  #define PG_GETARG_OID(n)     DatumGetObjectId(PG_GETARG_DATUM(n))
***************
*** 231,236 ****
--- 232,238 ----
  #define PG_RETURN_INT32(x)     return Int32GetDatum(x)
  #define PG_RETURN_UINT32(x)  return UInt32GetDatum(x)
  #define PG_RETURN_INT16(x)     return Int16GetDatum(x)
+ #define PG_RETURN_UINT8(x)     return UInt8GetDatum(x)
  #define PG_RETURN_CHAR(x)     return CharGetDatum(x)
  #define PG_RETURN_BOOL(x)     return BoolGetDatum(x)
  #define PG_RETURN_OID(x)     return ObjectIdGetDatum(x)
Index: src/include/postgres.h
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/src/include/postgres.h,v
retrieving revision 1.68
diff -c -r1.68 postgres.h
*** a/src/include/postgres.h    16 Jan 2004 20:51:30 -0000    1.68
--- b/src/include/postgres.h    9 Mar 2004 10:55:00 -0000
***************
*** 172,177 ****
--- 172,184 ----
  #define CharGetDatum(X) ((Datum) SET_1_BYTE(X))

  /*
+  * DatumGetInt8
+  *        Returns 8-bit integer value of a datum.
+  */
+
+ #define DatumGetInt8(X) ((int8) GET_1_BYTE(X))
+
+ /*
   * Int8GetDatum
   *        Returns datum representation for an 8-bit integer.
   */
Index: src/include/catalog/pg_cast.h
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/src/include/catalog/pg_cast.h,v
retrieving revision 1.10
diff -c -r1.10 pg_cast.h
*** a/src/include/catalog/pg_cast.h    29 Nov 2003 22:40:58 -0000    1.10
--- b/src/include/catalog/pg_cast.h    10 Mar 2004 10:30:24 -0000
***************
*** 64,82 ****

  /*
   * Numeric category: implicit casts are allowed in the direction
!  * int2->int4->int8->numeric->float4->float8, while casts in the
   * reverse direction are assignment-only.
   */
  DATA(insert (    20     21  714 a ));
  DATA(insert (    20     23  480 a ));
  DATA(insert (    20    700  652 i ));
  DATA(insert (    20    701  482 i ));
  DATA(insert (    20 1700 1781 i ));
  DATA(insert (    21     20  754 i ));
  DATA(insert (    21     23  313 i ));
  DATA(insert (    21    700  236 i ));
  DATA(insert (    21    701  235 i ));
  DATA(insert (    21 1700 1782 i ));
  DATA(insert (    23     20  481 i ));
  DATA(insert (    23     21  314 a ));
  DATA(insert (    23    700  318 i ));
--- 64,86 ----

  /*
   * Numeric category: implicit casts are allowed in the direction
!  * tinyint->int2->int4->int8->numeric->float4->float8, while casts in the
   * reverse direction are assignment-only.
   */
+ DATA(insert (   10     21 2516 i ));
+ DATA(insert (   10     23 2517 i ));
  DATA(insert (    20     21  714 a ));
  DATA(insert (    20     23  480 a ));
  DATA(insert (    20    700  652 i ));
  DATA(insert (    20    701  482 i ));
  DATA(insert (    20 1700 1781 i ));
+ DATA(insert (    21     10 2518 a ));
  DATA(insert (    21     20  754 i ));
  DATA(insert (    21     23  313 i ));
  DATA(insert (    21    700  236 i ));
  DATA(insert (    21    701  235 i ));
  DATA(insert (    21 1700 1782 i ));
+ DATA(insert (    23     10 2519 a ));
  DATA(insert (    23     20  481 i ));
  DATA(insert (    23     21  314 a ));
  DATA(insert (    23    700  318 i ));
***************
*** 106,118 ****
--- 110,125 ----
   * regproc<->regprocedure and regoper<->regoperator.  (Other coercions
   * between alias types must pass through OID.)
   */
+ DATA(insert (    10     26 2517 i ));
  DATA(insert (    20     26 1287 i ));
  DATA(insert (    21     26  313 i ));
  DATA(insert (    23     26    0 i ));
+ DATA(insert (    26     10 2519 a ));
  DATA(insert (    26     20 1288 a ));
  DATA(insert (    26     23    0 a ));
  DATA(insert (    26     24    0 i ));
  DATA(insert (    24     26    0 i ));
+ DATA(insert (    10     24 2517 i ));
  DATA(insert (    20     24 1287 i ));
  DATA(insert (    21     24  313 i ));
  DATA(insert (    23     24    0 i ));
***************
*** 248,253 ****
--- 255,262 ----
   * For historical reasons, most casts to TEXT are implicit.  This is BAD
   * and should be reined in.
   */
+ DATA(insert (    10     25 2514 e ));
+ DATA(insert (    25     10 2515 e ));
  DATA(insert (    20     25 1289 i ));
  DATA(insert (    25     20 1290 e ));
  DATA(insert (    21     25  113 i ));
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.320
diff -c -r1.320 pg_proc.h
*** a/src/include/catalog/pg_proc.h    14 Feb 2004 20:16:17 -0000    1.320
--- b/src/include/catalog/pg_proc.h    10 Mar 2004 10:20:44 -0000
***************
*** 3485,3490 ****
--- 3485,3512 ----
  DATA(insert OID = 2509 (  pg_get_expr           PGNSP PGUID 12 f f t f s 3 25 "25 26 16" _null_ pg_get_expr_ext -
_null_)); 
  DESCR("deparse an encoded expression with pretty-print option");

+ /* tinyint type functions */
+ DATA(insert OID = 2510 (  tinyintin           PGNSP PGUID 12 f f t f i 1 10 "2275" _null_ tinyintin - _null_ ));
+ DESCR("I/O");
+ DATA(insert OID = 2511 (  tinyintout           PGNSP PGUID 12 f f t f i 1 2275 "10" _null_ tinyintout - _null_ ));
+ DESCR("I/O");
+ DATA(insert OID = 2512 (  tinyintrecv           PGNSP PGUID 12 f f t f i 1 10 "2281" _null_ tinyintrecv - _null_ ));
+ DESCR("I/O");
+ DATA(insert OID = 2513 (  tinyintsend           PGNSP PGUID 12 f f t f i 1 17 "10" _null_ tinyintsend - _null_ ));
+ DESCR("I/O");
+ DATA(insert OID = 2514 (  tinyint_text           PGNSP PGUID 12 f f t f i 1  25 "10" _null_    tinyint_text - _null_
));
+ DESCR("convert tinyint to text");
+ DATA(insert OID = 2515 (  text_tinyint           PGNSP PGUID 12 f f t f i 1  10 "25" _null_    text_tinyint - _null_
));
+ DESCR("convert text to tinyint");
+ DATA(insert OID = 2516 (  tinytoi2               PGNSP PGUID 12 f f t f i 1  10 "21" _null_    tinytoi2 - _null_ ));
+ DESCR("convert tinyint to int2");
+ DATA(insert OID = 2517 (  tinytoi4               PGNSP PGUID 12 f f t f i 1  10 "23" _null_    tinytoi4 - _null_ ));
+ DESCR("convert tinyint to int4");
+ DATA(insert OID = 2518 (  i2totiny             PGNSP PGUID 12 f f t f i 1  21 "10" _null_    i2totiny - _null_ ));
+ DESCR("convert int2 to tinyint");
+ DATA(insert OID = 2519 (  i4totiny             PGNSP PGUID 12 f f t f i 1  23 "10" _null_    i4totiny - _null_ ));
+ DESCR("convert int4 to tinyint");
+
  /* non-persistent series generator */
  DATA(insert OID = 1066 (  generate_series PGNSP PGUID 12 f f t t v 3 23 "23 23 23" _null_ generate_series_step_int4 -
_null_)); 
  DESCR("non-persistent series generator");
Index: src/include/catalog/pg_type.h
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/src/include/catalog/pg_type.h,v
retrieving revision 1.150
diff -c -r1.150 pg_type.h
*** a/src/include/catalog/pg_type.h    24 Feb 2004 22:59:10 -0000    1.150
--- b/src/include/catalog/pg_type.h    9 Mar 2004 13:44:53 -0000
***************
*** 244,249 ****
--- 244,253 ----
  */

  /* OIDS 1 - 99 */
+ DATA(insert OID = 10 (    tinyint       PGNSP PGUID    1 t b t \054 0     0 tinyintin tinyintout tinyintrecv
tinyintsend- c p f 0 -1 0 _null_ _null_ )); 
+ DESCR("0 to 255, 1 byte storage");
+ #define TINYINTOID            15
+
  DATA(insert OID = 16 (    bool       PGNSP PGUID    1 t b t \054 0     0 boolin boolout boolrecv boolsend - c p f 0
-10 _null_ _null_ )); 
  DESCR("boolean, 'true'/'false'");
  #define BOOLOID            16
Index: src/include/utils/builtins.h
===================================================================
RCS file: /home/sun/sources/cvs/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.234
diff -c -r1.234 builtins.h
*** a/src/include/utils/builtins.h    3 Feb 2004 08:29:57 -0000    1.234
--- b/src/include/utils/builtins.h    10 Mar 2004 09:48:44 -0000
***************
*** 88,93 ****
--- 88,97 ----
  extern Datum char_text(PG_FUNCTION_ARGS);

  /* int.c */
+ extern Datum tinyintin(PG_FUNCTION_ARGS);
+ extern Datum tinyintout(PG_FUNCTION_ARGS);
+ extern Datum tinyintrecv(PG_FUNCTION_ARGS);
+ extern Datum tinyintsend(PG_FUNCTION_ARGS);
  extern Datum int2in(PG_FUNCTION_ARGS);
  extern Datum int2out(PG_FUNCTION_ARGS);
  extern Datum int2recv(PG_FUNCTION_ARGS);
***************
*** 103,108 ****
--- 107,114 ----
  extern Datum int4send(PG_FUNCTION_ARGS);
  extern Datum i2toi4(PG_FUNCTION_ARGS);
  extern Datum i4toi2(PG_FUNCTION_ARGS);
+ extern Datum tinyint_text(PG_FUNCTION_ARGS);
+ extern Datum text_tinyint(PG_FUNCTION_ARGS);
  extern Datum int2_text(PG_FUNCTION_ARGS);
  extern Datum text_int2(PG_FUNCTION_ARGS);
  extern Datum int4_text(PG_FUNCTION_ARGS);