Packages

epgsql

3.2.0
4.8.0 4.7.1 4.7.0 4.6.1 4.6.0 4.5.0 4.4.0 4.3.0 4.2.1 4.2.0 4.1.0 4.0.1 4.0.0 3.4.0 3.3.0 3.2.0 3.1.1 3.1.0

PostgreSQL Client

HexDocs

Current section

18 Versions

Jump to
Readme
18 Versions
0 Dependencies
Dependants
Files
Activity
Hide whitespace

Compare versions

12 files changed
+460 additions
-73 deletions
  @@ -4,6 +4,23 @@ Asynchronous fork of [wg/epgsql](https://github.com/wg/epgsql) originally here:
4 4 [mabrek/epgsql](https://github.com/mabrek/epgsql) and subsequently forked in order to
5 5 provide a common fork for community development.
6 6
7 + ## pgapp
8 +
9 + If you want to get up to speed quickly with code that lets you run
10 + Postgres queries, you might consider trying
11 + [epgsql/pgapp](https://github.com/epgsql/pgapp), which adds the
12 + following, on top of the epgsql driver:
13 +
14 + - A 'resource pool' (currently poolboy), which lets you decide how
15 + many Postgres workers you want to utilize.
16 + - Resilience against the database going down or other problems. The
17 + pgapp code will keep trying to reconnect to the database, but will
18 + not propagate the crash up the supervisor tree, so that, for
19 + instance, your web site will stay up even if the database is down
20 + for some reason. Erlang's "let it crash" is a good idea, but
21 + external resources going away might not be a good reason to crash
22 + your entire system.
23 +
7 24 ## Motivation
8 25
9 26 When you need to execute several queries, it involves a number network
  @@ -107,9 +124,9 @@ Asynchronous connect example (applies to **epgsqli** too):
107 124 }).
108 125
109 126 -type ok_reply(RowType) ::
110 - {ok, Count :: non_neg_integer()} | % select
111 - {ok, ColumnsDescription :: [#column{}], RowsValues :: [RowType]} | % update/insert
112 - {ok, Count :: non_neg_integer(), ColumnsDescription :: [#column{}], RowsValues :: [RowType]}. % update/insert + returning
127 + {ok, ColumnsDescription :: [#column{}], RowsValues :: [RowType]} | % select
128 + {ok, Count :: non_neg_integer()} | % update/insert/delete
129 + {ok, Count :: non_neg_integer(), ColumnsDescription :: [#column{}], RowsValues :: [RowType]}. % update/insert/delete + returning
113 130 -type error_reply() :: {error, query_error()}.
114 131 -type reply(RowType) :: ok_reply() | error_reply().
115 132
  @@ -244,7 +261,9 @@ Data Representation section below.
244 261 Asynchronous API `epgsqla:equery/3` requires you to parse statement beforehand
245 262
246 263 ```erlang
247 - Ref = epgsqla:equery(C, Statement, [Parameters]),
264 + #statement{types = Types} = Statement,
265 + TypedParameters = lists:zip(Types, Parameters),
266 + Ref = epgsqla:equery(C, Statement, [TypedParameters]),
248 267 receive
249 268 {C, Ref, Res} -> Res
250 269 end.
  @@ -253,9 +272,43 @@ end.
253 272 - `Statement` - parsed statement (see parse below)
254 273 - `Res` has same format as return value of `epgsql:equery/3`.
255 274
256 - `epgsqli:equery(C, Statement, [Parameters])` sends same set of messages as
275 + `epgsqli:equery(C, Statement, [TypedParameters])` sends same set of messages as
257 276 squery including final `{C, Ref, done}`.
258 277
278 + ## Prepared Query
279 + ```erlang
280 + {ok, Columns, Rows} = epgsql:prepared_query(C, StatementName, [Parameters]).
281 + {ok, Count} = epgsql:prepared_query(C, StatementName, [Parameters]).
282 + {ok, Count, Columns, Rows} = epgsql:prepared_query(C, StatementName, [Parameters]).
283 + {error, Error} = epgsql:prepared_equery(C, "non_existent_query", [Parameters]).
284 + ```
285 + `Parameters` - optional list of values to be bound to `$1`, `$2`, `$3`, etc.
286 + `StatementName` - name of query given with ```erlang epgsql:parse(C, StatementName, "select ...", []).```
287 +
288 + With prepared query one can parse a query giving it a name with `epgsql:parse` on start and reuse the name
289 + for all further queries with different parameters.
290 + ```erlang
291 + epgsql:parse(C, "inc", "select $1+1", []).
292 + epgsql:prepared_query(C, "inc", [4]).
293 + epgsql:prepared_query(C, "inc", [1]).
294 + ```
295 +
296 + Asynchronous API `epgsqla:prepared_query/3` requires you to parse statement beforehand
297 +
298 + ```erlang
299 + #statement{types = Types} = Statement,
300 + TypedParameters = lists:zip(Types, Parameters),
301 + Ref = epgsqla:prepared_query(C, Statement, [TypedParameters]),
302 + receive
303 + {C, Ref, Res} -> Res
304 + end.
305 + ```
306 +
307 + - `Statement` - parsed statement (see parse below)
308 + - `Res` has same format as return value of `epgsql:prepared_query/3`.
309 +
310 + `epgsqli:prepared_query(C, Statement, [TypedParameters])` sends same set of messages as
311 + squery including final `{C, Ref, done}`.
259 312
260 313 ## Parse/Bind/Execute
261 314
  @@ -365,6 +418,9 @@ PG type | Representation
365 418 record | `{int2, time, text, ...}` (decode only)
366 419 point | `{10.2, 100.12}`
367 420 int4range | `[1,5)`
421 + hstore | `{list({binary(), binary() | null})}`
422 + json/jsonb | `<<"{ \"key\": [ 1, 1.0, true, \"string\" ] }">>`
423 +
368 424
369 425 `timestamp` and `timestamptz` parameters can take `erlang:now()` format: `{MegaSeconds, Seconds, MicroSeconds}`
  @@ -1,18 +1,18 @@
1 1 {<<"name">>,<<"epgsql">>}.
2 - {<<"version">>,<<"3.1.1">>}.
2 + {<<"version">>,<<"3.2.0">>}.
3 + {<<"requirements">>,#{}}.
3 4 {<<"app">>,<<"epgsql">>}.
4 5 {<<"precompiled">>,false}.
5 6 {<<"description">>,<<"PostgreSQL Client">>}.
6 7 {<<"files">>,
7 - [{<<"src/epgsql.app.src">>,
8 - <<"{application,epgsql,\n [{description,\"PostgreSQL Client\"},\n {vsn,\"3.1.1\"},\n {modules,[]},\n {registered,[]},\n {applications,[kernel,stdlib,ssl]},\n {env,[]},\n {included_applications,[]},\n {licenses,[\"BSD\"]},\n {links,[{\"Github\",\"https://github.com/epgsql/epgsql\"}]}]}.\n">>},
9 - <<"src/epgsql.erl">>,<<"src/epgsql_binary.erl">>,
10 - <<"src/epgsql_fdatetime.erl">>,<<"src/epgsql_idatetime.erl">>,
11 - <<"src/epgsql_sock.erl">>,<<"src/epgsql_types.erl">>,
12 - <<"src/epgsql_wire.erl">>,<<"src/epgsqla.erl">>,<<"src/epgsqli.erl">>,
13 - <<"src/ewkb.erl">>,<<"include/epgsql.hrl">>,<<"include/epgsql_binary.hrl">>,
8 + [<<"src/epgsql.app.src">>,<<"LICENSE">>,<<"README.md">>,
9 + <<"include/epgsql.hrl">>,<<"include/epgsql_binary.hrl">>,
14 10 <<"include/epgsql_geometry.hrl">>,<<"rebar.config">>,<<"rebar.lock">>,
15 - <<"README.md">>,<<"LICENSE">>]}.
11 + <<"src/epgsql.erl">>,<<"src/epgsql_binary.erl">>,
12 + <<"src/epgsql_errcodes.erl">>,<<"src/epgsql_fdatetime.erl">>,
13 + <<"src/epgsql_idatetime.erl">>,<<"src/epgsql_sock.erl">>,
14 + <<"src/epgsql_types.erl">>,<<"src/epgsql_wire.erl">>,<<"src/epgsqla.erl">>,
15 + <<"src/epgsqli.erl">>,<<"src/ewkb.erl">>]}.
16 16 {<<"licenses">>,[<<"BSD">>]}.
17 17 {<<"links">>,[{<<"Github">>,<<"https://github.com/epgsql/epgsql">>}]}.
18 18 {<<"build_tools">>,[<<"rebar3">>]}.
  @@ -17,6 +17,7 @@
17 17 -record(error, {
18 18 severity :: fatal | error | atom(), %TODO: concretize
19 19 code :: binary(),
20 + codename :: atom(),
20 21 message :: binary(),
21 22 extra :: [{detail, binary()} | {hint, binary()} | {position, binary()}]
22 23 }).
  @@ -1,6 +1,6 @@
1 1 {application,epgsql,
2 2 [{description,"PostgreSQL Client"},
3 - {vsn,"3.1.1"},
3 + {vsn,"3.2.0"},
4 4 {modules,[]},
5 5 {registered,[]},
6 6 {applications,[kernel,stdlib,ssl]},
  @@ -8,6 +8,7 @@
8 8 get_parameter/2,
9 9 squery/2,
10 10 equery/2, equery/3, equery/4,
11 + prepared_query/3,
11 12 parse/2, parse/3, parse/4,
12 13 describe/2, describe/3,
13 14 bind/3, bind/4,
  @@ -17,12 +18,13 @@
17 18 sync/1,
18 19 cancel/1,
19 20 update_type_cache/1,
21 + update_type_cache/2,
20 22 with_transaction/2,
21 23 sync_on_error/2]).
22 24
23 25 -export_type([connection/0, connect_option/0,
24 26 connect_error/0, query_error/0,
25 - sql_query/0, bind_param/0,
27 + sql_query/0, bind_param/0, typed_param/0,
26 28 squery_row/0, equery_row/0, reply/1]).
27 29
28 30 -include("epgsql.hrl").
  @@ -51,14 +53,18 @@
51 53 | calendar:time() %actualy, `Seconds' may be float()
52 54 | calendar:datetime()
53 55 | {calendar:time(), Days::non_neg_integer(), Months::non_neg_integer()}
56 + | {list({binary(), binary() | null})} % hstore
54 57 | [bind_param()]. %array (maybe nested)
55 58
59 + -type typed_param() ::
60 + {epgsql_type(), bind_param()}.
61 +
56 62 -type squery_row() :: {binary()}.
57 63 -type equery_row() :: {bind_param()}.
58 64 -type ok_reply(RowType) ::
59 - {ok, Count :: non_neg_integer()} | % select
60 - {ok, ColumnsDescription :: [#column{}], RowsValues :: [RowType]} | % update/insert
61 - {ok, Count :: non_neg_integer(), ColumnsDescription :: [#column{}], RowsValues :: [RowType]}. % update/insert + returning
65 + {ok, ColumnsDescription :: [#column{}], RowsValues :: [RowType]} | % select
66 + {ok, Count :: non_neg_integer()} | % update/insert/delete
67 + {ok, Count :: non_neg_integer(), ColumnsDescription :: [#column{}], RowsValues :: [RowType]}. % update/insert/delete + returning
62 68 -type error_reply() :: {error, query_error()}.
63 69 -type reply(RowType) :: ok_reply(RowType) | error_reply().
64 70
  @@ -104,12 +110,22 @@ connect(C, Host, Username, Password, Opts) ->
104 110
105 111 -spec update_type_cache(connection()) -> ok.
106 112 update_type_cache(C) ->
107 - DynamicTypes = [<<"hstore">>,<<"geometry">>],
113 + update_type_cache(C, [<<"hstore">>,<<"geometry">>]).
114 +
115 + -spec update_type_cache(connection(), [binary()]) -> ok.
116 + update_type_cache(C, DynamicTypes) ->
108 117 Query = "SELECT typname, oid::int4, typarray::int4"
109 118 " FROM pg_type"
110 119 " WHERE typname = ANY($1::varchar[])",
111 - {ok, _, TypeInfos} = equery(C, Query, [DynamicTypes]),
112 - ok = gen_server:call(C, {update_type_cache, TypeInfos}).
120 + case equery(C, Query, [DynamicTypes]) of
121 + {ok, _, TypeInfos} ->
122 + ok = gen_server:call(C, {update_type_cache, TypeInfos});
123 + {error, {error, error, _, _,
124 + <<"column \"typarray\" does not exist in pg_type">>, _}} ->
125 + %% Do not fail connect if pg_type table in not in the expected
126 + %% format. Known to happen for Redshift which is based on PG v8.0.2
127 + ok
128 + end.
113 129
114 130 -spec close(connection()) -> ok.
115 131 close(C) ->
  @@ -147,6 +163,17 @@ equery(C, Name, Sql, Parameters) ->
147 163 Error
148 164 end.
149 165
166 + -spec prepared_query(C::connection(), Name::string(), Parameters::[bind_param()]) -> reply(equery_row()).
167 + prepared_query(C, Name, Parameters) ->
168 + case describe(C, statement, Name) of
169 + {ok, #statement{types = Types} = S} ->
170 + Typed_Parameters = lists:zip(Types, Parameters),
171 + gen_server:call(C, {prepared_query, S, Typed_Parameters}, infinity);
172 + Error ->
173 + Error
174 + end.
175 +
176 +
150 177 %% parse
151 178
152 179 parse(C, Sql) ->
  @@ -241,4 +268,3 @@ sync_on_error(C, Error = {error, _}) ->
241 268
242 269 sync_on_error(_C, R) ->
243 270 R.
244 -
Loading more files…