Function File: connection = pq_connect (settings)

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.

Demonstration 1

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

Demonstration 2

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