README 6.4 KB

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