Demo code showing how to do in-memory copy to Postgres

RamCopyToPg

    # 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
      # expects appropriate model for resetting sequence
      ar_model = options[:reset_id_sequence]
      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 CoreERR_SQLError, "SQL Server reports error code of #{conn.status}. Status code should have been 0"
        end
        # if not nil, consider ar_model is of ActiveRecord::Base and reset its sequence
        if ar_model
          AppUtils::DB::reset_id_sequence(ar_model)
        end
      end  # File::open
    end