Establishes a connection to a postgresql server according to
settings and returns an octave_pq_connection
object
connection which can be passed to other functions of the
package. There can be multiple connections. An
octave_pq_connection
object contains information on a
connection and will be internally altered by the packages functions
even though it is only passed as an argument, i.e. Octaves ’pass by
value’ semantics are not adhered to in this case.
settings can be set by setdbopts
(copied from Octaves
optimset
) and can contain (defaults depend on Postgresql):
host
(hostname), hostaddr
(numeric host address),
port
(port to connect to), dbname
(database to connect
to), user
(user name to connect as), password
,
connect_timeout
, options
(command-line options to send
to the server at run-time, see Postgresql documentation),
sslmode
(disable
, allow
, prefer
,
require
, verify-ca
, or verify-full
; see
Postgresql documentation of SSL support), sslcert
(file name
of client SSL certificate), sslkey
(location of secret key for
client certificate, file name or external OpenSSL engine
(colon-separated engine name and an engine-specific key identifier)),
sslrootcert
(file name of root SSL certificate), sslcrl
(file name of SSL certificate revocation list), krbsrvname
(kerberos service name), service
(service name in
pq_service.conf to use for additional parameters).
All these settings are passed to the server as they are, so it may be better to consult the postgresql documentation for them, e.g. the documentation of the PQconnectdb function in libpq.
See also: pq_exec_params, pq_update_types.
The following code
conn = pq_connect (setdbopts ("dbname", "test")); pq_exec_params (conn, "create table testtable (t text, i int2, b bytea);") pq_exec_params (conn, "insert into testtable values ($1, $2, $3);", {"name1", 1, uint8([2, 4, 5])}) pq_exec_params (conn, "insert into testtable values ($1, $2, $3);", {"name2", 2, uint8([7, 9, 3, 1])}) pq_exec_params (conn, "select * from testtable;") pq_exec_params (conn, "drop table testtable;") pq_close (conn);
Produces the following output
ans = 0 ans = 1 ans = 1 ans = scalar structure containing the fields: data = { [1,1] = name1 [2,1] = name2 [1,2] = 1 [2,2] = 2 [1,3] = 2 4 5 [2,3] = 7 9 3 1 } columns = { [1,1] = t [1,2] = i [1,3] = b } types = 1x3 struct array containing the fields: name is_array is_composite is_enum elements ans = 0
The following code
## recursive type, array-composite-array conn = pq_connect (setdbopts ("dbname", "test")); pq_exec_params (conn, "create type complex_bool_array_type as (b bool, ba bool[]);") pq_exec_params (conn, "create table complex_bool_array_array (a complex_bool_array_type[]);") pq_update_types (conn); pq_exec_params (conn, "insert into complex_bool_array_array values ($1);", {struct("ndims", 1, "data", {{{true, struct("ndims", 2, "data", {{true, false; true, true}})}; {false, struct("ndims", 1, "data", {{false; true}})}}})}, setdbopts ("param_types", {"complex_bool_array_type[]"})) data = pq_exec_params (conn, "select * from complex_bool_array_array;").data; ## copy in from variable pq_exec_params (conn, "copy complex_bool_array_array from stdin with binary;", setdbopts ("copy_in_data", data, "copy_in_from_variable", true, "copy_in_types", {"complex_bool_array_type[]"})) pq_exec_params (conn, "select * from complex_bool_array_array;") ## recursive type, composite-composite-array pq_exec_params (conn, "create type complex_complex_bool_array_type as (b bool, c complex_bool_array_type);") pq_exec_params (conn, "create table complex_complex_bool_array (a complex_complex_bool_array_type);") pq_update_types (conn); pq_exec_params (conn, "insert into complex_complex_bool_array values ($1);", {{false, {true, struct("ndims", 2, "data", {{true, false; true, true}})}}}, setdbopts ("param_types", {"complex_complex_bool_array_type"})) data = pq_exec_params (conn, "select * from complex_complex_bool_array").data; ## copy in from variable pq_exec_params (conn, "copy complex_complex_bool_array from stdin with binary;", setdbopts ("copy_in_data", data, "copy_in_from_variable", true, "copy_in_types", {"complex_complex_bool_array_type"})) pq_exec_params (conn, "select * from complex_complex_bool_array") pq_exec_params (conn, "drop table complex_complex_bool_array;") pq_exec_params (conn, "drop table complex_bool_array_array;") pq_exec_params (conn, "drop type complex_complex_bool_array_type;") pq_exec_params (conn, "drop type complex_bool_array_type;") pq_close (conn);
Produces the following output
ans = 0 ans = 0 ans = 1 ans = scalar structure containing the fields: data = { [1,1] = scalar structure containing the fields: data = { [1,1] = { [1,1] = 1 [2,1] = scalar structure containing the fields: data: 2x2 cell ndims: 1x1 scalar lbounds: 1x2 matrix } [2,1] = { [1,1] = 0 [2,1] = scalar structure containing the fields: data: 2x1 cell ndims: 1x1 scalar lbounds: 1x1 scalar } } ndims = 1 lbounds = 1 [2,1] = scalar structure containing the fields: data = { [1,1] = { [1,1] = 1 [2,1] = scalar structure containing the fields: data: 2x2 cell ndims: 1x1 scalar lbounds: 1x2 matrix } [2,1] = { [1,1] = 0 [2,1] = scalar structure containing the fields: data: 2x1 cell ndims: 1x1 scalar lbounds: 1x1 scalar } } ndims = 1 lbounds = 1 } columns = { [1,1] = a } types = scalar structure containing the fields: name = olaf.complex_bool_array_type is_array = 1 is_composite = 1 is_enum = 0 elements = 1x2 struct array containing the fields: name is_array is_composite is_enum elements ans = 0 ans = 0 ans = 1 ans = scalar structure containing the fields: data = { [1,1] = { [1,1] = 0 [2,1] = { [1,1] = 1 [2,1] = scalar structure containing the fields: data = { [1,1] = 1 [2,1] = 1 [1,2] = 0 [2,2] = 1 } ndims = 2 lbounds = 1 1 } } [2,1] = { [1,1] = 0 [2,1] = { [1,1] = 1 [2,1] = scalar structure containing the fields: data = { [1,1] = 1 [2,1] = 1 [1,2] = 0 [2,2] = 1 } ndims = 2 lbounds = 1 1 } } } columns = { [1,1] = a } types = scalar structure containing the fields: name = olaf.complex_complex_bool_array_type is_array = 0 is_composite = 1 is_enum = 0 elements = 1x2 struct array containing the fields: name is_array is_composite is_enum elements ans = 0 ans = 0 ans = 0 ans = 0
Package: database