# Module `Os_db.PGOCaml`

```ocaml
type 'a t
```
Database handle.

```ocaml
type 'a monad = 'a Lwt.t
```
```ocaml
type isolation = [ 
  | `Serializable
  | `Repeatable_read
  | `Read_committed
  | `Read_uncommitted
 ]
```
```ocaml
type access = [ 
  | `Read_write
  | `Read_only
 ]
```
```ocaml
exception Error of string
```
For library errors.

```ocaml
exception PostgreSQL_Error of string * (char * string) list
```
For errors generated by the PostgreSQL database back-end. The first argument is a printable error message. The second argument is the complete set of error fields returned from the back-end.

See `http://www.postgresql.org/docs/8.1/static/protocol-error-fields.html`


###### Connection management

```ocaml
type connection_desc = {
  user : string;
  port : int;
  password : string;
  host : [ `Hostname of string | `Unix_domain_socket_dir of string ];
  database : string;
}
```
```ocaml
val describe_connection : 
  ?host:string ->
  ?port:int ->
  ?user:string ->
  ?password:string ->
  ?database:string ->
  ?unix_domain_socket_dir:string ->
  unit ->
  connection_desc
```
Produce the actual, concrete connection parameters based on the values and \* availability of the various configuration variables.

```ocaml
val connection_desc_to_string : connection_desc -> string
```
Produce a human-readable textual representation of a concrete connection \* descriptor (the password is NOT included in the output of this function) \* for logging and error reporting purposes.

```ocaml
val connect : 
  ?host:string ->
  ?port:int ->
  ?user:string ->
  ?password:string ->
  ?database:string ->
  ?unix_domain_socket_dir:string ->
  ?desc:connection_desc ->
  unit ->
  'a t monad
```
Connect to the database.

The normal `$PGDATABASE`, etc. environment variables are available.

```ocaml
val close : 'a t -> unit monad
```
Close the database handle.

You must call this after you have finished with the handle, or else you will get leaked file descriptors.

```ocaml
val ping : 'a t -> unit monad
```
Ping the database.

If the database is not available, some sort of exception will be thrown.

```ocaml
val alive : 'a t -> bool monad
```
This function is a wrapper of `ping` that returns a boolean instead of raising an exception.


###### Transactions

```ocaml
val begin_work : 
  ?isolation:isolation ->
  ?access:access ->
  ?deferrable:bool ->
  'a t ->
  unit monad
```
Start a transaction.

```ocaml
val commit : 'a t -> unit monad
```
Perform a COMMIT operation on the database.

```ocaml
val rollback : 'a t -> unit monad
```
Perform a ROLLBACK operation on the database.

```ocaml
val transact : 
  'a t ->
  ?isolation:isolation ->
  ?access:access ->
  ?deferrable:bool ->
  ('a t -> 'b monad) ->
  'b monad
```
`transact db ?isolation ?access ?deferrable f` wraps your function `f` inside a transactional block. First it calls `begin_work` with `isolation`, `access` and `deferrable`, then calls `f` and do `rollback` if `f` raises an exception, `commit` otherwise.


###### Serial column

```ocaml
val serial : 'a t -> string -> int64 monad
```
This is a shorthand for `SELECT CURRVAL(serial)`.

For a table called `table` with serial column `id` you would typically call this as `serial dbh "table_id_seq"` after the previous INSERT operation to get the serial number of the inserted row.

```ocaml
val serial4 : 'a t -> string -> int32 monad
```
As [`serial`](./#val-serial) but assumes that the column is a SERIAL or SERIAL4 type.

```ocaml
val serial8 : 'a t -> string -> int64 monad
```
Same as [`serial`](./#val-serial).


###### Miscellaneous

```ocaml
val max_message_length : int ref
```
Maximum message length accepted from the back-end.

The default is `Sys.max_string_length`, which means that we will try to read as much data from the back-end as we can, and this may cause us to run out of memory (particularly on 64 bit machines), causing a possible denial of service.

You may want to set this to a smaller size to avoid this happening.

```ocaml
val verbose : int ref
```
Verbosity.

0 means don't print anything. 1 means print short error messages as returned from the back-end. 2 means print all messages as returned from the back-end.

Messages are printed on `stderr`. Default verbosity level is 1\.

```ocaml
val set_private_data : 'a t -> 'a -> unit
```
Attach some private data to the database handle.

NB. The pa\_pgsql camlp4 extension uses this for its own purposes, which means that in most programs you will not be able to attach private data to the database handle.

```ocaml
val private_data : 'a t -> 'a
```
Retrieve some private data previously attached to the database handle. If no data has been attached, raises `Not_found`.

NB. The pa\_pgsql camlp4 extension uses this for its own purposes, which means that in most programs you will not be able to attach private data to the database handle.

```ocaml
val uuid : 'a t -> string
```
Retrieve the unique identifier for this connection.

```ocaml
type pa_pg_data = (string, bool) Hashtbl.t
```
When using pa\_pgsql, database handles have type `PGOCaml.pa_pg_data PGOCaml.t`


###### Low level query interface \- DO NOT USE DIRECTLY

```ocaml
type oid = int32
```
```ocaml
val pp_oid : 
  Ppx_deriving_runtime.Format.formatter ->
  oid ->
  Ppx_deriving_runtime.unit
```
```ocaml
val show_oid : oid -> Ppx_deriving_runtime.string
```
```ocaml
type param = string option
```
```ocaml
type result = string option
```
```ocaml
type row = result list
```
```ocaml
val prepare : 
  'a t ->
  query:string ->
  ?name:string ->
  ?types:oid list ->
  unit ->
  unit monad
```
`prepare conn ~query ?name ?types ()` prepares the statement `query` and optionally names it `name` and sets the parameter types to `types`.

If no name is given, then the "unnamed" statement is overwritten. If no types are given, then the PostgreSQL engine infers types. Synchronously checks for errors.

```ocaml
val execute_rev : 
  'a t ->
  ?name:string ->
  ?portal:string ->
  params:param list ->
  unit ->
  row list monad
```
```ocaml
val execute : 
  'a t ->
  ?name:string ->
  ?portal:string ->
  params:param list ->
  unit ->
  row list monad
```
`execute conn ?name ~params ()` executes the named or unnamed statement `name`, with the given parameters `params`, returning the result rows (if any).

There are several steps involved at the protocol layer:

(1) a "portal" is created from the statement, binding the parameters in the statement (Bind).

(2) the portal is executed (Execute).

(3) we synchronise the connection (Sync).

The optional `?portal` parameter may be used to name the portal created in step (1) above (otherwise the unnamed portal is used). This is only important if you want to call [`describe_portal`](./#val-describe_portal) to find out the result types.

```ocaml
val cursor : 
  'a t ->
  ?name:string ->
  ?portal:string ->
  params:param list ->
  (row -> unit monad) ->
  unit monad
```
```ocaml
val close_statement : 'a t -> ?name:string -> unit -> unit monad
```
`close_statement conn ?name ()` closes a prepared statement and frees up any resources.

```ocaml
val close_portal : 'a t -> ?portal:string -> unit -> unit monad
```
`close_portal conn ?portal ()` closes a portal and frees up any resources.

```ocaml
val inject : 'a t -> ?name:string -> string -> row list monad
```
`inject conn ?name query` executes the statement `query` and optionally names it `name` and gives the result.

```ocaml
val alter : 'a t -> ?name:string -> string -> unit monad
```
`alter conn ?name query` executes the statement `query` and optionally names it `name`. Same as inject but ignoring the result.

```ocaml
type result_description = {
  name : string; (* Field name. *)
  table : oid option; (* OID of table. *)
  column : int option; (* Column number of field in table. *)
  field_type : oid; (* The type of the field. *)
  length : int; (* Length of the field. *)
  modifier : int32; (* Type modifier. *)
}
```
```ocaml
val pp_result_description : 
  Ppx_deriving_runtime.Format.formatter ->
  result_description ->
  Ppx_deriving_runtime.unit
```
```ocaml
val show_result_description : result_description -> Ppx_deriving_runtime.string
```
```ocaml
type row_description = result_description list
```
```ocaml
val pp_row_description : 
  Ppx_deriving_runtime.Format.formatter ->
  row_description ->
  Ppx_deriving_runtime.unit
```
```ocaml
val show_row_description : row_description -> Ppx_deriving_runtime.string
```
```ocaml
type params_description = param_description list
```
```ocaml
and param_description = {
  param_type : oid; (* The type of the parameter. *)
}
```
```ocaml
val describe_statement : 
  'a t ->
  ?name:string ->
  unit ->
  (params_description * row_description option) monad
```
`describe_statement conn ?name ()` describes the named or unnamed statement's parameter types and result types.

```ocaml
val describe_portal : 
  'a t ->
  ?portal:string ->
  unit ->
  row_description option monad
```
`describe_portal conn ?portal ()` describes the named or unnamed portal's result types.


###### Low level type conversion functions \- DO NOT USE DIRECTLY

```ocaml
val name_of_type : oid -> string
```
Returns the OCaml equivalent type name to the PostgreSQL type `oid`.

For instance, `name_of_type (Int32.of_int 23)` returns `"int32"` because the OID for PostgreSQL's internal `int4` type is `23`.

As another example, `name_of_type (Int32.of_int 25)` returns `"string"`.

```ocaml
type inet = Unix.inet_addr * int
```
```ocaml
type timestamptz = CalendarLib.Calendar.t * CalendarLib.Time_Zone.t
```
```ocaml
type int16 = int
```
```ocaml
type bytea = string
```
```ocaml
type point = float * float
```
```ocaml
type hstore = (string * string option) list
```
```ocaml
type numeric = string
```
```ocaml
type uuid = string
```
```ocaml
type jsonb = string
```
```ocaml
type bool_array = bool option list
```
```ocaml
type int16_array = int16 option list
```
```ocaml
type int32_array = int32 option list
```
```ocaml
type int64_array = int64 option list
```
```ocaml
type string_array = string option list
```
```ocaml
type float_array = float option list
```
```ocaml
type timestamp_array = CalendarLib.Calendar.t option list
```
```ocaml
type uuid_array = string option list
```
The following conversion functions are used by pa\_pgsql to convert values in and out of the database.

```ocaml
val string_of_oid : oid -> string
```
```ocaml
val string_of_bool : bool -> string
```
```ocaml
val string_of_int : int -> string
```
```ocaml
val string_of_int16 : int16 -> string
```
```ocaml
val string_of_int32 : int32 -> string
```
```ocaml
val string_of_int64 : int64 -> string
```
```ocaml
val string_of_float : float -> string
```
```ocaml
val string_of_point : point -> string
```
```ocaml
val string_of_hstore : hstore -> string
```
```ocaml
val string_of_numeric : numeric -> string
```
```ocaml
val string_of_uuid : uuid -> string
```
```ocaml
val string_of_jsonb : jsonb -> string
```
```ocaml
val string_of_inet : inet -> string
```
```ocaml
val string_of_timestamp : CalendarLib.Calendar.t -> string
```
```ocaml
val string_of_timestamptz : timestamptz -> string
```
```ocaml
val string_of_date : CalendarLib.Date.t -> string
```
```ocaml
val string_of_time : CalendarLib.Time.t -> string
```
```ocaml
val string_of_interval : CalendarLib.Calendar.Period.t -> string
```
```ocaml
val string_of_bytea : bytea -> string
```
```ocaml
val string_of_string : string -> string
```
```ocaml
val string_of_unit : unit -> string
```
```ocaml
val string_of_bool_array : bool_array -> string
```
```ocaml
val string_of_int16_array : int16_array -> string
```
```ocaml
val string_of_int32_array : int32_array -> string
```
```ocaml
val string_of_int64_array : int64_array -> string
```
```ocaml
val string_of_string_array : string_array -> string
```
```ocaml
val string_of_bytea_array : string_array -> string
```
```ocaml
val string_of_float_array : float_array -> string
```
```ocaml
val string_of_timestamp_array : timestamp_array -> string
```
```ocaml
val string_of_arbitrary_array : ('a -> string) -> 'a option list -> string
```
```ocaml
val string_of_uuid_array : uuid_array -> string
```
```ocaml
val comment_src_loc : unit -> bool
```
```ocaml
val find_custom_typconvs : 
  ?typnam:string ->
  ?lookin:string ->
  ?colnam:string ->
  ?argnam:string ->
  unit ->
  ((string * string) option, string) Rresult.result
```
```ocaml
val oid_of_string : string -> oid
```
```ocaml
val bool_of_string : string -> bool
```
```ocaml
val int_of_string : string -> int
```
```ocaml
val int16_of_string : string -> int16
```
```ocaml
val int32_of_string : string -> int32
```
```ocaml
val int64_of_string : string -> int64
```
```ocaml
val float_of_string : string -> float
```
```ocaml
val point_of_string : string -> point
```
```ocaml
val hstore_of_string : string -> hstore
```
```ocaml
val numeric_of_string : string -> numeric
```
```ocaml
val uuid_of_string : string -> uuid
```
```ocaml
val jsonb_of_string : string -> jsonb
```
```ocaml
val inet_of_string : string -> inet
```
```ocaml
val timestamp_of_string : string -> CalendarLib.Calendar.t
```
```ocaml
val timestamptz_of_string : string -> timestamptz
```
```ocaml
val date_of_string : string -> CalendarLib.Date.t
```
```ocaml
val time_of_string : string -> CalendarLib.Time.t
```
```ocaml
val interval_of_string : string -> CalendarLib.Calendar.Period.t
```
```ocaml
val bytea_of_string : string -> bytea
```
```ocaml
val unit_of_string : string -> unit
```
```ocaml
val bool_array_of_string : string -> bool_array
```
```ocaml
val int16_array_of_string : string -> int16_array
```
```ocaml
val int32_array_of_string : string -> int32_array
```
```ocaml
val int64_array_of_string : string -> int64_array
```
```ocaml
val string_array_of_string : string -> string_array
```
```ocaml
val float_array_of_string : string -> float_array
```
```ocaml
val timestamp_array_of_string : string -> timestamp_array
```
```ocaml
val arbitrary_array_of_string : (string -> 'a) -> string -> 'a option list
```
```ocaml
val bind : 'a monad -> ('a -> 'b monad) -> 'b monad
```
```ocaml
val return : 'a -> 'a monad
```