class Sequel::Postgres::Database

  1. lib/sequel/adapters/postgres.rb
Superclass: Sequel::Database



Public Instance methods

bound_variable_arg(arg, conn)

Convert given argument so that it can be used directly by pg. Currently, pg doesn't handle fractional seconds in Time/DateTime or blobs with “0”. Only public for use by the adapter, shouldn't be used by external code.

[show source]
    # File lib/sequel/adapters/postgres.rb
169 def bound_variable_arg(arg, conn)
170   case arg
171   when Sequel::SQL::Blob
172     {:value=>arg, :type=>17, :format=>1}
173   when DateTime, Time
174     literal(arg)
175   else
176     arg
177   end
178 end
call_procedure(name, *args)

Call a procedure with the given name and arguments. Returns a hash if the procedure returns a value, and nil otherwise. Example:

DB.call_procedure(:foo, 1, 2)
# CALL foo(1, 2)
[show source]
    # File lib/sequel/adapters/postgres.rb
185 def call_procedure(name, *args)
186   dataset.send(:call_procedure, name, args)
187 end

Connects to the database. In addition to the standard database options, using the :encoding or :charset option changes the client encoding for the connection, :connect_timeout is a connection timeout in seconds, :sslmode sets whether postgres's sslmode, and :notice_receiver handles server notices in a proc. :connect_timeout, :driver_options, :sslmode, and :notice_receiver are only supported if the pg driver is used.

[show source]
    # File lib/sequel/adapters/postgres.rb
196 def connect(server)
197   opts = server_opts(server)
198   if USES_PG
199     connection_params = {
200       :host => opts[:host],
201       :port => opts[:port],
202       :dbname => opts[:database],
203       :user => opts[:user],
204       :password => opts[:password],
205       :connect_timeout => opts[:connect_timeout] || 20,
206       :sslmode => opts[:sslmode],
207       :sslrootcert => opts[:sslrootcert]
208     }.delete_if { |key, value| blank_object?(value) }
209     connection_params.merge!(opts[:driver_options]) if opts[:driver_options]
210     conn = Adapter.connect(opts[:conn_str] || connection_params)
212     conn.instance_variable_set(:@prepared_statements, {})
214     if receiver = opts[:notice_receiver]
215       conn.set_notice_receiver(&receiver)
216     end
217   else
218     unless typecast_value_boolean(@opts.fetch(:force_standard_strings, true))
219       raise Error, "Cannot create connection using postgres-pr unless force_standard_strings is set"
220     end
222     conn = Adapter.connect(
223       (opts[:host] unless blank_object?(opts[:host])),
224       opts[:port] || 5432,
225       nil, '',
226       opts[:database],
227       opts[:user],
228       opts[:password]
229     )
230   end
232   conn.instance_variable_set(:@db, self)
233   if USES_PG && conn.respond_to?(:type_map_for_queries=) && defined?(PG_QUERY_TYPE_MAP)
234     conn.type_map_for_queries = PG_QUERY_TYPE_MAP
235   end
237   if encoding = opts[:encoding] || opts[:charset]
238     if conn.respond_to?(:set_client_encoding)
239       conn.set_client_encoding(encoding)
240     else
241       conn.async_exec("set client_encoding to '#{encoding}'")
242     end
243   end
245   connection_configuration_sqls(opts).each{|sql| conn.execute(sql)}
246   conn
247 end

Always false, support was moved to pg_extended_date_support extension. Needs to stay defined here so that sequel_pg works.

[show source]
    # File lib/sequel/adapters/postgres.rb
251 def convert_infinite_timestamps
252   false
253 end

Enable pg_extended_date_support extension if symbol or string is given.

[show source]
    # File lib/sequel/adapters/postgres.rb
256 def convert_infinite_timestamps=(v)
257   case v
258   when Symbol, String, true
259     extension(:pg_extended_date_support)
260     self.convert_infinite_timestamps = v
261   end
262 end
copy_into(table, opts=OPTS)

copy_into uses PostgreSQL's +COPY FROM STDIN+ SQL statement to do very fast inserts into a table using input preformatting in either CSV or PostgreSQL text format. This method is only supported if pg 0.14.0+ is the underlying ruby driver. This method should only be called if you want results returned to the client. If you are using +COPY FROM+ with a filename, you should just use run instead of this method.

The following options are respected:


The columns to insert into, with the same order as the columns in the input data. If this isn't given, uses all columns in the table.


The data to copy to PostgreSQL, which should already be in CSV or PostgreSQL text format. This can be either a string, or any object that responds to each and yields string.


The format to use. text is the default, so this should be :csv or :binary.


An options SQL string to use, which should contain comma separated options.


The server on which to run the query.

If a block is provided and :data option is not, this will yield to the block repeatedly. The block should return a string, or nil to signal that it is finished.

[show source]
    # File lib/sequel/adapters/postgres.rb
399 def copy_into(table, opts=OPTS)
400   data = opts[:data]
401   data = Array(data) if data.is_a?(String)
403   if block_given? && data
404     raise Error, "Cannot provide both a :data option and a block to copy_into"
405   elsif !block_given? && !data
406     raise Error, "Must provide either a :data option or a block to copy_into"
407   end
409   synchronize(opts[:server]) do |conn|
410     conn.execute(copy_into_sql(table, opts))
411     begin
412       if block_given?
413         while buf = yield
414           conn.put_copy_data(buf)
415         end
416       else
417         data.each{|buff| conn.put_copy_data(buff)}
418       end
419     rescue Exception => e
420       conn.put_copy_end("ruby exception occurred while copying data into PostgreSQL")
421     ensure
422       conn.put_copy_end unless e
423       while res = conn.get_result
424         raise e if e
425         check_database_errors{res.check}
426       end
427     end
428   end 
429 end
copy_table(table, opts=OPTS)

copy_table uses PostgreSQL's +COPY TO STDOUT+ SQL statement to return formatted results directly to the caller. This method is only supported if pg is the underlying ruby driver. This method should only be called if you want results returned to the client. If you are using +COPY TO+ with a filename, you should just use run instead of this method.

The table argument supports the following types:


Uses the first argument directly as literal SQL. If you are using a version of PostgreSQL before 9.0, you will probably want to use a string if you are using any options at all, as the syntax Sequel uses for options is only compatible with PostgreSQL 9.0+. This should be the full COPY statement passed to PostgreSQL, not just the SELECT query. If a string is given, the :format and :options options are ignored.


Uses a query instead of a table name when copying.


Uses a table name (usually a symbol) when copying.

The following options are respected:


The format to use. text is the default, so this should be :csv or :binary.


An options SQL string to use, which should contain comma separated options.


The server on which to run the query.

If a block is provided, the method continually yields to the block, one yield per row. If a block is not provided, a single string is returned with all of the data.

[show source]
    # File lib/sequel/adapters/postgres.rb
349 def copy_table(table, opts=OPTS)
350   synchronize(opts[:server]) do |conn|
351     conn.execute(copy_table_sql(table, opts))
352     begin
353       if block_given?
354         while buf = conn.get_copy_data
355           yield buf
356         end
357         b = nil
358       else
359         b =
360         b << buf while buf = conn.get_copy_data
361       end
363       res = conn.get_last_result
364       if !res || res.result_status != 1
365         raise PG::NotAllCopyDataRetrieved, "Not all COPY data retrieved"
366       end
368       b
369     rescue => e
370       raise_error(e, :disconnect=>true)
371     ensure
372       if buf && !e
373         raise DatabaseDisconnectError, "disconnecting as a partial COPY may leave the connection in an unusable state"
374       end
375     end
376   end 
377 end
[show source]
    # File lib/sequel/adapters/postgres.rb
264 def disconnect_connection(conn)
265   conn.finish
266 rescue PGError, IOError
267   nil
268 end

Return a hash of information about the related PGError (or Sequel::DatabaseError that wraps a PGError), with the following entries (any of which may be nil):


The schema name related to the error


The table name related to the error


the column name related to the error


The constraint name related to the error


The datatype name related to the error


The severity of the error (e.g. “ERROR”)


The SQL state code related to the error


A single line message related to the error


Any detail supplementing the primary message


Possible suggestion about how to fix the problem


Character offset in statement submitted by client where error occurred (starting at 1)


Character offset in internal statement where error occurred (starting at 1)


Text of internally-generated statement where error occurred


PostgreSQL source file where the error occurred


Line number of PostgreSQL source file where the error occurred


Function in PostgreSQL source file where the error occurred

This requires a PostgreSQL 9.3+ server and 9.3+ client library, and ruby-pg 0.16.0+ to be supported.

[show source]
    # File lib/sequel/adapters/postgres.rb
293 def error_info(e)
294   e = e.wrapped_exception if e.is_a?(DatabaseError)
295   r = e.result
296   {
297     :schema => r.error_field(::PG::PG_DIAG_SCHEMA_NAME),
298     :table => r.error_field(::PG::PG_DIAG_TABLE_NAME),
299     :column => r.error_field(::PG::PG_DIAG_COLUMN_NAME),
300     :constraint => r.error_field(::PG::PG_DIAG_CONSTRAINT_NAME),
301     :type => r.error_field(::PG::PG_DIAG_DATATYPE_NAME),
302     :severity => r.error_field(::PG::PG_DIAG_SEVERITY),
303     :sql_state => r.error_field(::PG::PG_DIAG_SQLSTATE),
304     :message_primary => r.error_field(::PG::PG_DIAG_MESSAGE_PRIMARY),
305     :message_detail => r.error_field(::PG::PG_DIAG_MESSAGE_DETAIL),
306     :message_hint => r.error_field(::PG::PG_DIAG_MESSAGE_HINT),
307     :statement_position => r.error_field(::PG::PG_DIAG_STATEMENT_POSITION),
308     :internal_position => r.error_field(::PG::PG_DIAG_INTERNAL_POSITION),
309     :internal_query => r.error_field(::PG::PG_DIAG_INTERNAL_QUERY),
310     :source_file => r.error_field(::PG::PG_DIAG_SOURCE_FILE),
311     :source_line => r.error_field(::PG::PG_DIAG_SOURCE_LINE),
312     :source_function => r.error_field(::PG::PG_DIAG_SOURCE_FUNCTION)
313   }
314 end
execute(sql, opts=OPTS, &block)
[show source]
    # File lib/sequel/adapters/postgres.rb
317 def execute(sql, opts=OPTS, &block)
318   synchronize(opts[:server]){|conn| check_database_errors{_execute(conn, sql, opts, &block)}}
319 end
listen(channels, opts=OPTS, &block)

Listens on the given channel (or multiple channels if channel is an array), waiting for notifications. After a notification is received, or the timeout has passed, stops listening to the channel. Options:


An object that responds to call that is called with the underlying connection after the LISTEN statement is sent, but before the connection starts waiting for notifications.


Whether to continually wait for notifications, instead of just waiting for a single notification. If this option is given, a block must be provided. If this object responds to call, it is called with the underlying connection after each notification is received (after the block is called). If a :timeout option is used, and a callable object is given, the object will also be called if the timeout expires. If :loop is used and you want to stop listening, you can either break from inside the block given to listen, or you can throw :stop from inside the :loop object's call method or the block.


The server on which to listen, if the sharding support is being used.


How long to wait for a notification, in seconds (can provide a float value for fractional seconds). If this object responds to call, it will be called and should return the number of seconds to wait. If the loop option is also specified, the object will be called on each iteration to obtain a new timeout value. If not given or nil, waits indefinitely.

This method is only supported if pg is used as the underlying ruby driver. It returns the channel the notification was sent to (as a string), unless :loop was used, in which case it returns nil. If a block is given, it is yielded 3 arguments:

  • the channel the notification was sent to (as a string)

  • the backend pid of the notifier (as an integer),

  • and the payload of the notification (as a string or nil).

[show source]
    # File lib/sequel/adapters/postgres.rb
454 def listen(channels, opts=OPTS, &block)
455   check_database_errors do
456     synchronize(opts[:server]) do |conn|
457       begin
458         channels = Array(channels)
459         channels.each do |channel|
460           sql = "LISTEN ".dup
461           dataset.send(:identifier_append, sql, channel)
462           conn.execute(sql)
463         end
464         opts[:after_listen].call(conn) if opts[:after_listen]
465         timeout = opts[:timeout]
466         if timeout
467           timeout_block = timeout.respond_to?(:call) ? timeout : proc{timeout}
468         end
470         if l = opts[:loop]
471           raise Error, 'calling #listen with :loop requires a block' unless block
472           loop_call = l.respond_to?(:call)
473           catch(:stop) do
474             while true
475               t = timeout_block ? [] : []
476               conn.wait_for_notify(*t, &block)
477      if loop_call
478             end
479           end
480           nil
481         else
482           t = timeout_block ? [] : []
483           conn.wait_for_notify(*t, &block)
484         end
485       ensure
486         conn.execute("UNLISTEN *")
487       end
488     end
489   end
490 end