Skip site navigation (1) Skip section navigation (2)

libpq binary transfer of the numeric data type

From: Eliot Simcoe <esimcoe(at)mac(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: libpq binary transfer of the numeric data type
Date: 2004-06-14 18:25:45
Message-ID: 405AD008-BE30-11D8-B774-000A959CE7FA@mac.com (view raw or flat)
Thread:
Lists: pgsql-interfaces
Hello Everyone,

I am writing an Objective-C wrapper of libpq and would like to 
implement automatic type conversion between the standard PostgreSQL 
data types and associated Objective-C wrapper classes.

it has been a struggle using the binary I/O method supported by 
PQexecParams() for both parameter passing and result retrieval due to 
the lack of documentation on the internal data representations used by 
PostgreSQL, and after reading a number of posts from 1999 frowning upon 
its usage I am aware of the issues associate with this approach. I've 
searched the lists numerous times for information on the binary 
representations of a number of types such as Date and Numeric in vain. 
Finally, after about a day of poking header files (literally) I was 
able to extract the internal data structures from the PgSQL source 
code. Now I am facing the challenge of deciphering their usage.

typedef struct
{
	SInt32		varlen;					// variable size (std varlena header)
	SInt16		n_weight;				// weight of 1st digit
	UInt16		n_sign_dscale;			// sign + display scale
	SInt8		n_data[1];				// digits
}
sql_numeric;

Could someone please explain to me what the varlen field is used for? 
It seems to hold enormous values in my testing, so it couldn't possibly 
be the allocated number of bytes or bits... or the number of digits 
stored in the n_data field. The n_data field itself is a mystery. I 
can't seem to figure out exactly how it is storing the digits. 
According to numeric.c n_data is an array of signed short integers. 
Each short integer represents four numbers in some sort of 
binary-coded-decimal like means. I can extract the values in the first 
two shorts, but the rest of the results seem garbled. Here is some 
source code illustrating what I have done:

sql_numeric numeric = (sql_numeric)(*((sql_numeric *)[data bytes]));
UInt16 dscale = numeric.n_sign_dscale & NUMERIC_DISPLAY_SCALE_MASK;
NSMutableString *string = [NSMutableString string];
UInt32 i, c = ([data length] - 8) / 2;
UInt32 partShorts = (dscale + 3) / 4;
UInt32 wholeShorts = c - partShorts;
		
for( i = 0 ; i < wholeShorts ; i++ )
{
	SInt16 value = (SInt16)(((SInt16 *)(numeric.n_data))[i]);
	char block[5];
	UInt32 j = 0;
	char t;
			
	sprintf( block, "%d", value );
			
	while( (t = block[j++]) != '\0' )
	{
		[string appendFormat:@"%c", t];
	}
}
		
[string appendString:@"."];
		
for( i = wholeShorts ; i < c ; i++ )
{
	SInt16 value = (SInt16)(((SInt16 *)(numeric.n_data))[i]);
	char block[5];
	UInt32 j = 0;
	char t;
			
	sprintf( block, "%d", value );
			
	while( (t = block[j++]) != '\0' )
	{
		[string appendFormat:@"%c", t];
	}
}
		
NSLog( string );

This is obviously a quick hack in a desperate attempt to figure out how 
the data is packed, but the results are strange. The input to the above 
is the numeric 123456777654.890123. The output (as displayed by 
NSLog()) is 123456774353.-267846, indicating that the first two shorts 
are decoded "correctly", but the rest is completely wrong. I am 
puzzled.

Anyway, my first question is: How can I convert this into a double 
value? I know it isn't ideal, but at least it will allow the developer 
to access the number is some sort of meaningful way.

My second question is related to an apparent difference between the 
documentation and one of the examples provided. Is the data always 
converted to big endian byte order when transferred over the network, 
or is it dependent on the serving architecture? It seems to me that 
this should be standardized, and the third example program seems to 
state that it is. I have read, however, posts to these lists indicating 
that it is not.

Anyway, sorry for this long winded post, but I have been struggling for 
a while and would really appreciate any help anyone can give me.
Thanks in advance,

Eliot Simcoe
Software Engineer
Vantine Imaging, LLC.
315-790-1773

Responses

pgsql-interfaces by date

Next:From: David StanawayDate: 2004-06-14 18:57:09
Subject: Re: libpq binary transfer of the numeric data type
Previous:From: L J BayukDate: 2004-06-10 23:44:54
Subject: Re: libpq 7.4 and binary cursor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group