Cleveland Metroparks GIS needed an ArcGIS tool to load shapefiles to a PostgreSQL database.  The following Python code accomplished this task.

import arcgisscripting, os, sys, traceback, string

try:

    gp = arcgisscripting.create()

    # +------------------------------------+

    # | insert your custom code following  |

    # | this line.                         |

    # +------------------------------------+

   

    shapefile = gp.GetParameterAsText(0)

    dbase = gp.GetParameterAsText(1)

    schema = gp.GetParameterAsText(2)

    path = shapefile

    (dirName, fileName) = os.path.split(path)

    (fileBaseName, fileExtension) = os.path.splitext(fileName)

    gp.AddMessage ("Loading " + fileName + "...")

    os.environ['Path'] = r"C:\Program Files\PostgreSQL\8.3\bin"

    cmd = r"shp2pgsql.exe " + shapefile + " " + schema + "." + fileBaseName + '> ' + "C:\\workspace\\"

    gp.AddMessage (cmd)

    os.system(cmd)

    path2 = "C:\\workspace\\" + fileBaseName + ".sql"

    (dirName2, fileName2) = os.path.split(path)

    (fileBaseName2, fileExtension2) = os.path.splitext(fileName2)

    gp.AddMessage (fileName2)

    input_file = open("C:\\workspace\\" + fileName2, "r")

    output_file = open("C:\\workspace\\" + fileBaseName2 + "2" + fileExtension2, 'w')

   

    stext = "int8"

    rtext = "int4"

       

    for line in input_file:

        newline = line.replace(stext, rtext)

        output_file.write(newline)

                           

    input_file.close

    output_file.flush()

    output_file.close

   

    input_file2 = open("C:\\workspace\\" + fileBaseName2 + "2" + fileExtension2, "r")

    output_file2 = open("C:\\workspace\\" + fileBaseName2 + "3" + fileExtension2, 'w')

   

    itext = "numeric"

    otext = "float8"

   

    for line in input_file2:

        newline2 = line.replace(itext, otext)

        output_file2.write(newline2)

   

    input_file2.close

    output_file2.flush()

    output_file2.close

    os.environ['Path'] = r"C:\Program Files\PostgreSQL\8.3\bin"

    cmd = r"psql " + "-U " + "postgres " + "-d " + dbase + "-f " + "C:\\workspace\\" + fileBaseName2 + "3" + fileExtension2

    gp.AddMessage (cmd)

    os.system(cmd)

    gp.AddMessage (shapefile + " was loaded sucessfully")

except:

    # +------------------------------------+

    # | the following code will capture    |

    # | error messages from Python and     |

    # | ArcGIS and diplay them to the      |

    # | ArcGIS geoprocessing tool window.  |

    # +------------------------------------+

    # get the traceback object

    tb = sys.exc_info()[2]

    # tbinfo contains the line number that the code failed on and the code from that line

    tbinfo = traceback.format_tb(tb)[0]

    # concatenate information together concerning the error into a message string

    pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n    " +   str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n"

    # generate a message string for any geoprocessing tool errors

    msgs = "GP ERRORS:\n" + gp.GetMessages(2) + "\n"

    # return gp messages for use with a script tool

    gp.AddError(msgs)

    gp.AddError(pymsg)

    # print messages for use in Python/PythonWin

    print msgs

    print pymsg

Note: Requires ArcGIS 9.2, Python 2.5, and PostgreSQL 8.x.