Skip to content
This repository
tree: 3318bd5d64
Fetching contributors…

Cannot retrieve contributors at this time

file 143 lines (100 sloc) 5.421 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
Erlang PostgreSQL Database Client

* Connect

  {ok, C} = pgsql:connect(Host, [Username], [Password], Opts).

  Host - host to connect to.
  Username - username to connect as, defaults to $USER.
  Password - optional password to authenticate with.
  Opts - property list of extra options. Supported properties:

    + {database, String}
    + {port, Integer}
    + {ssl, Atom} true | false | required
    + {ssl_opts, List} see ssl application docs in OTP
    + {timeout, Integer} milliseconds, defaults to 5000
    + {async, Pid} see Asynchronous Messages section

  {ok, C} = pgsql:connect("localhost", "username", [{database, "test_db"}]).
  ok = pgsql:close(C).

  The timeout parameter will trigger an {error, timeout} result when the
  server fails to respond within Timeout milliseconds. This timeout applies
  to the initial connection attempt and any subsequent queries.

* Simple Query

  {ok, Columns, Rows} = pgsql:squery(C, "select ...").
  {ok, Count} = pgsql:squery(C, "update ...").
  {ok, Count, Columns, Rows} = pgsql:squery(C, "insert ... returning ...").

  {error, Error} = pgsql:squery(C, "invalid SQL").

  Columns - list of column records, see pgsql.hrl for definition.
  Rows - list of tuples, one for each row.
  Count - integer count of rows inserted/updated/etc

  The simple query protocol returns all columns as text (Erlang binaries)
  and does not support binding parameters.

* Extended Query

  {ok, Columns, Rows} = pgsql:equery(C, "select ...", [Parameters]).
  {ok, Count} = pgsql:equery(C, "update ...", [Parameters]).
  {ok, Count, Columns, Rows} = pgsql:equery(C, "insert ... returning ...", [Parameters]).

  {error, Error} = pgsql:equery(C, "invalid SQL", [Parameters]).

  Parameters - optional list of values to be bound to $1, $2, $3, etc.

  The extended query protocol combines parse, bind, and execute using
  the unnamed prepared statement and portal. A "select" statement returns
  {ok, Columns, Rows}, "insert/update/delete" returns {ok, Count} or
  {ok, Count, Columns, Rows} when a "returning" clause is present. When
  an error occurs, all statements result in {error, #error{}}.

  PostgreSQL's binary format is used to return integers as Erlang
  integers, floats as floats, bytea/text/varchar columns as binaries,
  bools as true/false, etc. For details see pgsql_binary.erl and the
  Data Representation section below.

* Parse/Bind/Execute

  {ok, Statement} = pgsql:parse(C, [StatementName], Sql, [ParameterTypes]).

  StatementName - optional, reusable, name for the prepared statement.
  ParameterTypes - optional list of PostgreSQL types for each parameter.

  For valid type names see pgsql_types.erl.

  ok = pgsql:bind(C, Statement, [PortalName], ParameterValues).

  PortalName - optional name for the result portal.

  {ok | partial, Rows} = pgsql:execute(C, Statement, [PortalName], [MaxRows]).
  {ok, Count} = pgsql:execute(C, Statement, [PortalName]).
  {ok, Count, Rows} = pgsql:execute(C, Statement, [PortalName]).

  PortalName - optional portal name used in bind/4.
  MaxRows - maximum number of rows to return (0 for all rows).

  execute returns {partial, Rows} when more rows are available.

  ok = pgsql:close(C, Statement).
  ok = pgsql:close(C, statement | portal, Name).
  ok = pgsql:sync(C).

  All functions return {error, Error} when an error occurs.

* Data Representation

  null = null
  bool = true | false
  char = $A | binary
  intX = 1
  floatX = 1.0
  date = {Year, Month, Day}
  time = {Hour, Minute, Second.Microsecond}
  timetz = {time, Timezone}
  timestamp = {date, time}
  timestamptz = {date, time}
  interval = {time, Days, Months}
  text = <<"a">>
  varchar = <<"a">>
  bytea = <<1, 2>>
  array = [1, 2, 3]

  record = {int2, time, text, ...} (decode only)

* Errors

  Errors originating from the PostgreSQL backend are returned as {error, #error{}},
  see pgsql.hrl for the record definition. epgsql functions may also return
  {error, What} where What is one of the following:

  {unsupported_auth_method, Method} - required auth method is unsupported
  timeout - request timed out
  closed - connection was closed
  sync_required - error occured and pgsql:sync must be called

* Asynchronous Messages

  PostgreSQL may deliver two types of asynchronous message: "notices" in response
  to notice and warning messages generated by the server, and "notifications" which
  are generated by the LISTEN/NOTIFY mechanism.

  Passing the {async, Pid} option to pgsql:connect will result in these async
  messages being sent to the specified process, otherwise they will be dropped.

  Message formats:

    {pgsql, Connection, {notification, Channel, Pid, Payload}}

      Connection - connection the notification occured on

      Channel - channel the notification occured on
      Pid - database session pid that sent notification
      Payload - optional payload, only available from PostgreSQL >= 9.0

    {pgsql, Connection, {notice, Error}}

      Connection - connection the notice occured on
      Error - an #error{} record, see pgsql.hrl
Something went wrong with that request. Please try again.