[postgis-devel] Three fixes to pgsql2shp

[Kris Jurka]

Attached are three fixes to pgsql2shp.

1) 001-know-more-type-size.patch, Improve the exporter's knowledge of pg 
type sizes to fix timestamp and include timestamptz, time, timetz, and 
uuid.  I was dumping a timestamptz column and it decided it was only 8 
bytes long (the internal size, not the text size, see #3) so all of my 
timestamps were truncated.

2) 002-no-implicit-casts-with-83.patch, Starting with postgresql 8.3 
implicit casts to text have been removed.  Therefore the call to 
octet_length will fail.

3) 003-attlen-is-useless.patch.  pg_attribute.attlen is the internal 
storage size of the type, which has nothing to do with the exported size 
in text format.  If we don't know anything about the type by oid, our 
only option is to do a brute force search through the table.

Below are test cases showing the problems that are fixed by these 
patches.  This was tested on postgresql 8.3.7 running postgis 1.3, but 
these patches were developed against SVN trunk as they are completely a 
client side issue and are really unrelated to geometry handling.

CREATE TABLE t (a timestamptz, b xml, c oid, d geometry);
INSERT INTO t VALUES
     (now(), '<a>hi</a>', 12345678, geomfromewkt('POINT(3 4)'));

1) Truncated timestamptz column:

$ pgsql2shp -ftypetest testdb 'SELECT a, d FROM t'
$ dbview typetest.dbf
A          : 2009-11-

2) Can't find length of xml type:
$ pgsql2shp -ftypetest testdb 'SELECT b, d FROM t'

Preparing table for user query... Done.
Initializing... Querying for maximum field length: ERROR:  function 
octet_length(xml) does not exist
LINE 1: select max(octet_length("b")) from "__pgsql2shp9894_tmp_tabl...
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.

3) Wrong length for unknown fixed size type results in a truncated value:

$ pgsql2shp -ftypetest testdb 'SELECT c, d FROM t'
$ dbview typetest.dbf
C          : 1234

So with all of these patches applied we get:

$ pgsql2shp -ftypetest testdb 'SELECT a, b, c, d FROM t'
$ dbview typetest.dbf
A          : 2009-11-12 03:02:28.262377-08
B          : <a>hi</a>
C          : 12345678


Kris Jurka
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 002-no-implicit-casts-with-83.patch
Type: text/x-patch
Size: 834 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20091112/f71479ec/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 003-attlen-is-useless.patch
Type: text/x-patch
Size: 2247 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20091112/f71479ec/attachment-0001.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 001-know-more-type-sizes.patch
Type: text/x-patch
Size: 1483 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20091112/f71479ec/attachment-0002.bin>