Postgres reference

PGconn

The class to access PostgreSQL database. All other functionality of libpq save the large object to a file.

For example, to send query to the database on the localhost.

require "postgres"
conn = PGconn.connect("localhost", 5432, "", "", "test1")
# or: conn = PGconn.open('dbname=test1')
res = conn.exec("select * from a;")
    

super class:

Object

class methods:

connect(pghost, pgport, pgoptions, pgtty, dbname, login, passwd) new(pghost, pgport, pgoptions, pgtty, dbname, login, passwd) open(string)

Connect to the PostgreSQL server. Options are:

pghost : Server hostname(string)

pgport : Server port number(integer)

pgoptions : backend options(string)

pgtty : tty to print backend debug message(string)

dbname : connecting database name(string)

login : login user name(string)

passwd : login password(string)

Options in string format (separated by whitespace) are:

host=name : Server hostname(string) (defaults to localhost)

hostaddr=addr : Server host IP address(string)

port=number : Server port number(integer) (default: 5432)

options=string : backend options(string) (sent to server, not well explained)

tty=anything : ignored, used to be debug TTY(string)

dbname=name : connecting database name(string) (default: your username)

user=username : login user name(string) (default: your username)

password=censored : login password(string)

sslmode=mode : how to treat SSL(string) (one of disable, allow, prefer, require)

service=name : service name in pg_service.conf(string)

connect_timeout=seconds : how long to wait for a connection(integer) (0 means forever)

On failure, it raises PGError exception.

methods:

db

Returns the connected database name.

host

Returns the connected server name.

user

Returns the authenticated user name.

options

Returns backend option string.

port

Returns the connected server port number.

tty

Returns the connected pgtty.

error

Returns the error message about connection.

finish

close

Closes the backend connection.

reset

Resets the backend connection. This method closes the backend connection and tries to re-connect.

trace(port)

Enables tracing message passing between backend. The trace message will be written to the port object, which is the instance of the class File.

untrace

Disables the message tracing.

exec(sql)

Sends SQL query request specified by sql to the PostgreSQL. Returns the PGresult instance on success. On failure, it raises PGError exception.

query(sql)

Sends SQL query request specified by sql to the PostgreSQL.Returns an Array as the resulting tuple on success. On failure, it returns nil, and error detail can be obtained by error.

async_exec(sql)

Sends SQL asynchronous query request specified by sql to the PostgreSQL. Returns the PGresult instance on success. On failure, it raises PGError exception.

async_query(sql)

Sends SQL asynchronous query request specified by sql to the PostgreSQL.Returns an Array as the resulting tuple on success. On failure, it returns nil, and error detail can be obtained by error.

get_notify

Returns the array of the unprocessed notifiers. If there is no unprocessed notifier, it returns nil.

insert_table(table, array)

Inserts contents of the array into the table.

getline

Reads a line from the backend server into internal buffer. Returns nil for EOF, 0 for success, 1 for buffer overflowed. You need to ensure single "." from backend to confirm transmission completion. The sample program psql.rb treats this copy protocol right.

putline(string)

Sends the string to the backend server. Users must send a single "." to denote the end of data transmission.

endcopy

Waits until the backend completes the copying. You should call this method after putline, or getline.Returns nil on success, raises an exception otherwise.

set_client_encoding

Set client encoding(String).

client_encoding

Returns client encoding(String).

set_notice_processor(proc)

Control reporting of notice and warning messages generated by the backend server (with Proc or anything else responding to :call). Pass nil to disable processing of the messages.

lo_import(file)

Import a file to a large object. Return the PGlarge instance on success. On failure, it raises PGError exception.

lo_export(oid, file)

Save a large object of oid to a file.

lo_create([mode])

Return the PGlarge instance on success. On failure, it raises PGError exception.

lo_open(oid, [mode])

Open a large object of oid. Return the PGlarge instance on success. The mode argument specifies the mode for the opened large object, which is either "INV_READ", or "INV_WRITE". If mode On failure, it raises PGError exception. If mode omitted, the default is "INV_READ"

lo_unlink(oid)

Unlink (deletes) the postgres large object of oid.

PGresult

The class to represent the query result tuples. The object of this class is created as the result of every query. You may need to invoke clear method for the finished object for better memory performance.

super class:

Object

methods:

status

Returns the status of the query. The status value is either:

EMPTY_QUERY

COMMAND_OK

TUPLES_OK

COPY_OUT

COPY_IN

result

Returns the query result tuple in the array.

fields

Returns the array of the fields of the query result.

num_tuples

Returns the number of tuples of the query result.

num_fields

Returns the number of fields of the query result.

fieldname(index)

Returns the field name corresponding field index.

fieldnum(name)

Returns the index of the nameed field.

type(index)

Returns the integer corresponding the type of the field. The field indicator starts from zero.

size(index)

Returns the size of the field in bytes. Returns -1 if the field is variable sized.

getvalue(tup_num, field_num)

Returns the field value.

getlength(tup_num, field_num)

Returns the length of the field in bytes.

cmdtuples

the number of tuples (rows) affected by the SQL command.

cmdstatus

Returns the status string of the last query command.

oid

Returns the oid of the inserted row, or nil if the last statement was not an INSERT

clear

Clears the PGresult object as the result of the query.

PGlarge

The class to access large objects. The object of this class is created as the result of lo_import, lo_create, and lo_open.

super class:

Object

methods:

open([mode])

Open a large object. The mode argument specifies the mode for the opened large object, which is either "INV_READ","INV_READ".

close

Close a large object. Closed when they are garbage-collected.

read([length])

Attempts to read length bytes from large object. If no length given, reads all data.

write(str)

Write the string to the large object. Return the number of bytes written.

seek(offset, whence)

Move the large object pointer to the offset. The value for whence are SEEK_SET, SEEK_CUR, and SEEK_END.Or it is 0,1,2.

tell

Return the current position of the large object pointer.

unlink

Delete large object.

oid

Return the large object oid.

size

Return the size of large object.

export(file)

Save a large object of oid to a file.