Method: PG::Connection#copy_data

Defined in:
lib/pg/connection.rb

#copy_data(sql, coder = nil) ⇒ Object

call-seq:

conn.copy_data( sql ) {|sql_result| ... } -> PG::Result

Execute a copy process for transfering data to or from the server.

This issues the SQL COPY command via #exec. The response to this (if there is no error in the command) is a PG::Result object that is passed to the block, bearing a status code of PGRES_COPY_OUT or PGRES_COPY_IN (depending on the specified copy direction). The application should then use #put_copy_data or #get_copy_data to receive or transmit data rows and should return from the block when finished.

#copy_data returns another PG::Result object when the data transfer is complete. An exception is raised if some problem was encountered, so it isn't required to make use of any of them. At this point further SQL commands can be issued via #exec. (It is not possible to execute other SQL commands using the same connection while the COPY operation is in progress.)

This method ensures, that the copy process is properly terminated in case of client side or server side failures. Therefore, in case of blocking mode of operation, #copy_data is preferred to raw calls of #put_copy_data, #get_copy_data and #put_copy_end.

Example with CSV input format:

conn.exec "create table my_table (a text,b text,c text,d text)"
conn.copy_data "COPY my_table FROM STDIN CSV" do
  conn.put_copy_data "some,data,to,copy\n"
  conn.put_copy_data "more,data,to,copy\n"
end

This creates my_table and inserts two CSV rows.

The same with text format encoder PG::TextEncoder::CopyRow and Array input:

enco = PG::TextEncoder::CopyRow.new
conn.copy_data "COPY my_table FROM STDIN", enco do
  conn.put_copy_data ['some', 'data', 'to', 'copy']
  conn.put_copy_data ['more', 'data', 'to', 'copy']
end

Example with CSV output format:

conn.copy_data "COPY my_table TO STDOUT CSV" do
  while row=conn.get_copy_data
    p row
  end
end

This prints all rows of my_table to stdout:

"some,data,to,copy\n"
"more,data,to,copy\n"

The same with text format decoder PG::TextDecoder::CopyRow and Array output:

deco = PG::TextDecoder::CopyRow.new
conn.copy_data "COPY my_table TO STDOUT", deco do
  while row=conn.get_copy_data
    p row
  end
end

This receives all rows of my_table as ruby array:

["some", "data", "to", "copy"]
["more", "data", "to", "copy"]


150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
# File 'lib/pg/connection.rb', line 150

def copy_data( sql, coder=nil )
	res = exec( sql )

	case res.result_status
	when PGRES_COPY_IN
		begin
			if coder
				old_coder = self.encoder_for_put_copy_data
				self.encoder_for_put_copy_data = coder
			end
			yield res
		rescue Exception => err
			errmsg = "%s while copy data: %s" % [ err.class.name, err.message ]
			put_copy_end( errmsg )
			get_result
			raise
		else
			put_copy_end
			get_last_result
		ensure
			self.encoder_for_put_copy_data = old_coder if coder
		end

	when PGRES_COPY_OUT
		begin
			if coder
				old_coder = self.decoder_for_get_copy_data
				self.decoder_for_get_copy_data = coder
			end
			yield res
		rescue Exception => err
			cancel
			while get_copy_data
			end
			while get_result
			end
			raise
		else
			res = get_last_result
			if !res || res.result_status != PGRES_COMMAND_OK
				while get_copy_data
				end
				while get_result
				end
				raise PG::NotAllCopyDataRetrieved, "Not all COPY data retrieved"
			end
			res
		ensure
			self.decoder_for_get_copy_data = old_coder if coder
		end

	else
		raise ArgumentError, "SQL command is no COPY statement: #{sql}"
	end
end