On Thu, Jun 16, 2022 at 03:41:50PM -0400, Tom Lane wrote:
> Robert Haas <[email protected]> writes:
> > On Tue, Jun 14, 2022 at 1:40 PM Mark Wong <[email protected]> wrote:
> >> I've created a function for each data type with the idea that an example
> >> for handling a specific data type can be more easily reviewed by looking
> >> in a single place.
> >> I've added examples for REAL, TIMESTAMP WITHOUT TIME ZONE, and BOOLEAN
> >> to try to illustrate how testlibpq3.sql and testlibpq3.c will grow if
> >> this is a good way to go.
>
> > I'm not sure that we want to let these test programs grow very large.
> > In particular, I don't think we should let ourselves get sucked into
> > adding an example for every data type under the sun -- if that's
> > wanted, the solution is perhaps to add documentation for the binary
> > formats, not hide impromptu documentation inside a test program. But
> > doing this much seems OK to me.
>
> Yeah, "hiding impromptu documentation inside a test program" is what
> this looks like, and I'm not sure that's a reasonable way to go.
>
> (1) Who's going to think to look in src/test/examples/testlibpq3.c for
> documentation of binary formats?
>
> (2) The useful details are likely to get buried in notational and
> portability concerns, as I think your build failure illustrates.
>
> (3) I bet few if any packagers install these files, so that the new
> info would be unavailable to many people.
>
> I think some new appendix in the main SGML docs would be the appropriate
> place if we want to provide real documentation.
Just wanted to do another quick check in before I go too far. How do
does this start look? Extending the libpq section with a code snippet
and description per data type?
Regards,
Mark
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 37ec3cb4e5..44c60223ee 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -8874,6 +8874,109 @@ int PQisthreadsafe();
</para>
</sect1>
+ <sect1 id="libpq-binary-format">
+ <title>Binary Format</title>
+ <para>
+ Various <application>libpq</application> functions support sending or
+ receiving data in binary format. Data in binary format requires the
+ application to be handle any differences between the system and the network
+ byte order.
+ </para>
+
+ <para>
+ <xref linkend="libpq-example-3"/> demonstrates how to write a complete
+ program that uses <application>libpq</application> functions that request
+ data in binary foramt. This example shows how to handle only a few data
+ types. This section will detail the handling of as many of the data types
+ as possible. See <xref linkend="datatype"/> for descriptions of each of
the
+ built-in data types.
+ </para>
+
+ <sect2 id="libpq-binary-boolean">
+ <title><type>boolean</type></title>
+ <para>
+ A <type>boolean</type> is transmitted as single byte that, when cast to
+ an <literal>int</literal>, will be <literal>0</literal> for
+ <literal>false</literal> and <literal>1</literal> for
+ <literal>true</literal>.
+ </para>
+<programlisting>
+<![CDATA[
+int value;
+
+ptr = PQgetvalue(res, row_number, column_number);
+value = (int) *ptr;
+printf("%d\n", value);
+]]>
+</programlisting>
+ </sect2>
+
+ <sect2 id="libpq-binary-real">
+ <title><type>real</type></title>
+ <para>
+ A <type>real</type> is composed of 4 bytes and needs to be handled
+ correctly for byte order.
+ </para>
+<programlisting>
+<![CDATA[
+union {
+ int i;
+ float f;
+} value;
+
+ptr = PQgetvalue(res, row_number, column_number);
+val.i = ntohl(*((uint32_t *) ptr));
+printf("%f\n", value.f);
+]]>
+</programlisting>
+ </sect2>
+
+ <sect2 id="libpq-binary-timestamp-without-time-zone">
+ <title><type>timestamp without time zone</type></title>
+ <para>
+ A <type>timestamp without time zone</type> is a 64-bit data type
+ representing the number of microseconds since January 1, 2000. It can be
+ converted into a broken-down time representation by converting the time
+ into seconds and saving the microseconds elsewhere.
+ </para>
+ <para>
+ Note that in C time is counted from January 1, 1970 so this discrepency
+ needs to be accounted for in addition to handling the network byte order.
+ </para>
+<programlisting>
+<![CDATA[
+#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */
+#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */
+#define SECS_PER_DAY 86400
+
+uint64_t value;
+
+struct tm *tm;
+time_t timep;
+uint32_t mantissa;
+
+ptr = PQgetvalue(res, column_number, row_number);
+/* Note ntohll() is not implemented on all platforms. */
+val = ntohll(*((uint64_t *) ptr));
+
+timep = val / (uint64_t) 1000000 +
+ (uint64_t) (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
+ (uint64_t) SECS_PER_DAY;
+mantissa = val - (uint64_t) (timep -
+ (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY) *
+ (uint64_t) 1000000;
+
+/* Assume and print timestamps in GMT for simplicity. */
+tm = gmtime(&timep);
+
+printf("%04d-%02d-%02d %02d:%02d:%02d.%06d\n",
+ tm->tm_year + 1900, tm->tm_mon + 1, tm->tm_mday, tm->tm_hour,
+ tm->tm_min, tm->tm_sec, mantissa);
+]]>
+</programlisting>
+ </sect2>
+
+ </sect1>
<sect1 id="libpq-build">
<title>Building <application>libpq</application> Programs</title>
