postgres-fast-import.rb

# NOTE: This is sample code that may not include  libraries that are required by this method
# This method is just to demonstrate how to do fast imports from STDIN directly to Postgres
# This method was written a long time ago, so it may no longer function as described
# The following code was ripped from a library and not run, so it may not be fully functional as published

module TableLoader
    # return each line with the newline value for the platform in question
    # we strip any newlines from the end of each line and replace them with
    # Ruby "\n" which should be platform specific
    def TableLoader::get_line_from_file(file_handle)
      retval = file_handle.gets
      if retval
        retval.chomp!
        retval += "\n"
      end
    end

    # pass in an appropriate AR Model for :reset_id_sequence option if you want to reset the sequence after loading
    # Options include:
    # :delete_existing_data => truncates all data before loading (default: false)
    # :sql_parameters => permits specifying sql parameters for COPY such as DELIMETER and NULL (default: '')
    # :skip_header_row => skips the first row of table when true (default: false)
    def TableLoader.copy_from_file_to_table(table_name, field_names, import_file_path, options = {})
      delete_existing_data = options[:delete_existing_data] || false
      sql_parameters = options[:sql_parameters] || ''
      skip_header_row = options[:skip_header_row] || false
      File::open(import_file_path, "r") do |import_file|
        # eat first line if there is a header row
        import_file::gets if skip_header_row
        # CUSTOMSQL create dynamic import sql code using 'COPY FROM' sql statement
        import_sql = ''
        import_sql += "TRUNCATE TABLE #{table_name}; " if delete_existing_data
        import_sql += "COPY #{table_name} "
        import_sql += "(#{field_names}) "
        import_sql += "FROM STDIN #{sql_parameters}; "
        # exit if there are no data rows to process
        if import_file.eof? : return false; end
        # CUSTOMSQL we obtain a raw PGconn SQL connection to the database so we can ship data
        # directly to STDIN on the Postgres SQL connection
        raw_conn = ActiveRecord::Base.connection.raw_connection
        # execute the import SQL, which will leave the connection open so we can ship
        # raw records directly to STDIN on the server, via PGconn.putline command (below)
        raw_conn.exec(import_sql)

        # write all data rows to sql server - we write one line at a time to make debugging easier
        loop = TableLoader::get_line_from_file(import_file)
        while !loop.blank?
          raw_conn.putline(loop) if !loop.blank?
          loop = TableLoader::get_line_from_file(import_file)
        end
        # this alternative writes the entire file to disk at once
        # raw_conn.putline(import_file::gets(nil))
        raw_conn.putline("\\.\n") # send Postgres EOF signal: \.<new_line>
        raw_conn.endcopy # tell the driver we are done sending data
        if raw_conn.status != 0
          raise "SQL Server reports error code of #{conn.status}. Status code should have been 0"
        end
        # Nb. consider resetting the sequence ID for tables after import if ActiveRecord is used
      end  # File::open
    end
end # Module