README 7.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230
  1. Erlang PostgreSQL Database Client
  2. Asynchronous fork of https://github.com/wg/epgsql
  3. It passes all tests from original driver except 3 timeout tests.
  4. Difference highlights (see CHANGES for full list):
  5. + 3 API sets: pgsql, apgsql and ipgsql:
  6. pgsql maintains backwards compatibility with original driver API,
  7. apgsql delivers complete results as regular erlang messages,
  8. ipgsql delivers results as messages incrementally (row by row)
  9. + internal queue of client requests, so you don't need to wait for response to send next request
  10. + single process to hold driver state and receive socket data
  11. + execute several prepared statements as a batch
  12. + bind timestamps in erlang:now() format
  13. * Known problems
  14. Timeout supplied at connect time works as socket connect timeout not query timeout.
  15. SSL performance degrades if driver process has large inbox (thousands of messages).
  16. * Connect
  17. {ok, C} = pgsql:connect(Host, [Username], [Password], Opts).
  18. Host - host to connect to.
  19. Username - username to connect as, defaults to $USER.
  20. Password - optional password to authenticate with.
  21. Opts - property list of extra options. Supported properties:
  22. + {database, String}
  23. + {port, Integer}
  24. + {ssl, Atom} true | false | required
  25. + {ssl_opts, List} see ssl application docs in OTP
  26. + {timeout, Integer} milliseconds, defaults to 5000
  27. + {async, Pid} see Server Notifications section
  28. {ok, C} = pgsql:connect("localhost", "username", [{database, "test_db"}]).
  29. ok = pgsql:close(C).
  30. The timeout parameter will trigger an {error, timeout} result when the
  31. socket fails to connect within Timeout milliseconds.
  32. Asynchronous connect example (applies to ipgsql too):
  33. {ok, C} = apgsql:start_link(),
  34. Ref = apgsql:connect(C, "localhost", "username", [{database, "test_db"}]),
  35. receive
  36. {C, Ref, connected} ->
  37. {ok, C};
  38. {C, Ref, Error = {error, _}} ->
  39. Error;
  40. {'EXIT', C, _Reason} ->
  41. {error, closed}
  42. end.
  43. * Simple Query
  44. {ok, Columns, Rows} = pgsql:squery(C, "select ...").
  45. {ok, Count} = pgsql:squery(C, "update ...").
  46. {ok, Count, Columns, Rows} = pgsql:squery(C, "insert ... returning ...").
  47. {error, Error} = pgsql:squery(C, "invalid SQL").
  48. Columns - list of column records, see pgsql.hrl for definition.
  49. Rows - list of tuples, one for each row.
  50. Count - integer count of rows inserted/updated/etc
  51. The simple query protocol returns all columns as text (Erlang binaries)
  52. and does not support binding parameters.
  53. apgsql:squery returns result as a single message:
  54. Ref = apgsql:squery(C, Sql),
  55. receive
  56. {C, Ref, Res} -> Res
  57. end.
  58. Res has same format as return value of pgsql:squery.
  59. ipgsql:squery returns result incrementally for each query inside Sql and
  60. for each row:
  61. Ref = ipgsql:squery(C, Sql),
  62. receive
  63. {C, Ref, {columns, Columns}} ->
  64. %% columns description
  65. Columns;
  66. {C, Ref, {data, Row}} ->
  67. %% single data row
  68. Row;
  69. {C, Ref, {error, _E} = Error} ->
  70. Error;
  71. {C, Ref, {complete, {_Type, Count}}} ->
  72. %% execution of one insert/update/delete has finished
  73. {ok, Count}; % affected rows count
  74. {C, Ref, {complete, _Type}} ->
  75. %% execution of one select has finished
  76. ok;
  77. {C, Ref, done} ->
  78. %% execution of all queries from Sql has finished
  79. done;
  80. end.
  81. * Extended Query
  82. {ok, Columns, Rows} = pgsql:equery(C, "select ...", [Parameters]).
  83. {ok, Count} = pgsql:equery(C, "update ...", [Parameters]).
  84. {ok, Count, Columns, Rows} = pgsql:equery(C, "insert ... returning ...", [Parameters]).
  85. {error, Error} = pgsql:equery(C, "invalid SQL", [Parameters]).
  86. Parameters - optional list of values to be bound to $1, $2, $3, etc.
  87. The extended query protocol combines parse, bind, and execute using
  88. the unnamed prepared statement and portal. A "select" statement returns
  89. {ok, Columns, Rows}, "insert/update/delete" returns {ok, Count} or
  90. {ok, Count, Columns, Rows} when a "returning" clause is present. When
  91. an error occurs, all statements result in {error, #error{}}.
  92. PostgreSQL's binary format is used to return integers as Erlang
  93. integers, floats as floats, bytea/text/varchar columns as binaries,
  94. bools as true/false, etc. For details see pgsql_binary.erl and the
  95. Data Representation section below.
  96. Ref = apgsql:equery(C, Sql, [Parameters]),
  97. receive
  98. {C, Ref, Res} -> Res
  99. end.
  100. Res has same format as return value of pgsql:equery.
  101. ipgsql:equery(C, Sql, [Parameters]) sends same set of messages as squery,
  102. including final {C, Ref, done}.
  103. * Parse/Bind/Execute
  104. {ok, Statement} = pgsql:parse(C, [StatementName], Sql, [ParameterTypes]).
  105. StatementName - optional, reusable, name for the prepared statement.
  106. ParameterTypes - optional list of PostgreSQL types for each parameter.
  107. For valid type names see pgsql_types.erl.
  108. apgsql:parse sends {C, Ref, {ok, Statement} | {error, Reason}}.
  109. ipgsql:parse sends:
  110. {C, Ref, {types, Types}}
  111. {C, Ref, {columns, Columns}}
  112. {C, Ref, no_data} if statement will not return rows
  113. {C, Ref, {error, Reason}}
  114. ok = pgsql:bind(C, Statement, [PortalName], ParameterValues).
  115. PortalName - optional name for the result portal.
  116. both apgsql:bind and ipgsql:bind send {C, Ref, ok | {error, Reason}}
  117. {ok | partial, Rows} = pgsql:execute(C, Statement, [PortalName], [MaxRows]).
  118. {ok, Count} = pgsql:execute(C, Statement, [PortalName]).
  119. {ok, Count, Rows} = pgsql:execute(C, Statement, [PortalName]).
  120. PortalName - optional portal name used in bind/4.
  121. MaxRows - maximum number of rows to return (0 for all rows).
  122. execute returns {partial, Rows} when more rows are available.
  123. ok = pgsql:close(C, Statement).
  124. ok = pgsql:close(C, statement | portal, Name).
  125. ok = pgsql:sync(C).
  126. All functions return {error, Error} when an error occurs.
  127. * Data Representation
  128. null = null
  129. bool = true | false
  130. char = $A | binary
  131. intX = 1
  132. floatX = 1.0
  133. date = {Year, Month, Day}
  134. time = {Hour, Minute, Second.Microsecond}
  135. timetz = {time, Timezone}
  136. timestamp = {date, time}
  137. timestamptz = {date, time}
  138. interval = {time, Days, Months}
  139. text = <<"a">>
  140. varchar = <<"a">>
  141. bytea = <<1, 2>>
  142. array = [1, 2, 3]
  143. record = {int2, time, text, ...} (decode only)
  144. timestamp and timestamptz parameters can take erlang:now() format {MegaSeconds, Seconds, MicroSeconds}
  145. * Errors
  146. Errors originating from the PostgreSQL backend are returned as {error, #error{}},
  147. see pgsql.hrl for the record definition. epgsql functions may also return
  148. {error, What} where What is one of the following:
  149. {unsupported_auth_method, Method} - required auth method is unsupported
  150. timeout - request timed out
  151. closed - connection was closed
  152. sync_required - error occured and pgsql:sync must be called
  153. * Server Notifications
  154. PostgreSQL may deliver two types of asynchronous message: "notices" in response
  155. to notice and warning messages generated by the server, and "notifications" which
  156. are generated by the LISTEN/NOTIFY mechanism.
  157. Passing the {async, Pid} option to pgsql:connect will result in these async
  158. messages being sent to the specified process, otherwise they will be dropped.
  159. Message formats:
  160. {pgsql, Connection, {notification, Channel, Pid, Payload}}
  161. Connection - connection the notification occured on
  162. Channel - channel the notification occured on
  163. Pid - database session pid that sent notification
  164. Payload - optional payload, only available from PostgreSQL >= 9.0
  165. {pgsql, Connection, {notice, Error}}
  166. Connection - connection the notice occured on
  167. Error - an #error{} record, see pgsql.hrl