PostgreSQL DBE Driver

DOCUMENTATION

• NAME
• SYNOPSIS
• EXAMPLE
• DESCRIPTION
• Connection Parameters
• Connection Attributes
• Connection Functions
• Object Mode
• PL/pgSQL
• Large Objects
• LIMITATIONS
• KNOWN ERRORS
• AUTHORS
• COPYRIGHT

NAME

SYNOPSIS

  use DBE;
  
  $dsn = "Driver=PostgreSQL;Db=postgres";
  $con = DBE->connect( $dsn );
  
  $res = $con->query( 'SELECT * FROM table' );
  
  $stmt = $con->prepare( 'SELECT * FROM table' );
  $res = $stmt->execute();

EXAMPLE

  use DBE;
  
  # connect with parameters as hash
  $con = DBE->connect(
      'driver' => 'PostgreSQL',
      'user' => 'db_user',
      'password' => 'db_pass',
      'db' => 'test',
      'charset' => 'utf-8',
      'reconnect' => 1,
  );
  
  # create a table
  $con->do(
      qq/
      CREATE TABLE cfg_config (
          cfg_index SERIAL PRIMARY KEY,
          cfg_name VARCHAR(64),
          cfg_value VARCHAR(64),
          UNIQUE (cfg_name)
      )
      /
  );
  
  # insert an entry
  $con->do(
      "INSERT INTO cfg_config (cfg_name,cfg_value) VALUES(?,?)",
      'foo', 'bar'
  );
  
  # select the entry
  $res = $con->query(
      "SELECT cfg_value FROM cfg_config WHERE cfg_name = 'foo'"
  );
  
  ($foo) = $res->fetch_row();
  print "\$foo = '$foo'\n";
  
  # output:
  # $foo = 'bar'

DESCRIPTION

Connection Parameters

  use DBE;
  
  DBE->connect( 'Driver=PostgreSQL;Db=postgres;User=postgres' );

• DRIVER | PROVIDER [PostgreSQL|Pg]
  Driver must be "PostgreSQL" or "Pg".

• DB | DATABASE [dbname]
  Name of the database to connect to.

• HOST [hostname]
  Name of host to connect to. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default behavior when host is not specified is to connect to a Unix-domain socket in /tmp (or whatever socket directory was specified when PostgreSQL was built).
  On machines without Unix-domain sockets, the default is to connect to localhost.

• USER | UID [user]
  PostgreSQL user name to connect as. Defaults to be the same as the operating system name of the user running the application.

• AUTH | PWD | PASSWORD [pwd]
  Password to be used if the server demands password authentication.

• SSLMODE [disable|allow|prefer|require]
  This option determines whether or with what priority an SSL connection will be negotiated with the server. There are four modes: disable will attempt only an unencrypted SSL connection; allow will negotiate, trying first a non-SSL connection, then if that fails, trying an SSL connection; prefer (the default) will negotiate, trying first an SSL connection, then if that fails, trying a regular non-SSL connection; require will try only an SSL connection.

• OPTIONS [string]
  Command-line options to be sent to the server.

• CONNECTTIMEOUT | CONNECT_TIMEOUT [int]
  Maximum wait for connection, in seconds. Zero or not specified means wait indefinitely. It is not recommended to use a timeout of less than 2 seconds.

• KRBSRVNAME [string]
  Kerberos service name to use when authenticating with Kerberos 5 or GSSAPI. This must match the service name specified in the server configuration for Kerberos authentication to succeed.

• GSSLIB [string]
  GSS library to use for GSSAPI authentication. Only used on Windows. Set to gssapi to force libpq to use the GSSAPI library for authentication instead of the default SSPI.

• SERVICE [string]
  Service name to use for additional parameters. It specifies a service name in pg_service.conf that holds additional connection parameters. This allows applications to specify only a service name so connection parameters can be centrally maintained.

• AUTOCOMMIT | AUTO_COMMIT [1|0|Yes|No|True|False]
  Enable or disable auto commit after connect. PostgreSQL enables it by default.

• ISOFORMAT | ISO_FORMAT [1|0|Yes|No|True|False]
  Show date and time values in iso format. Enabled by default. (In object mode this setting has no efect.)

Connection Attributes

• CHARSET | CHARACTERSET [string]
  Connection character set.

• LOBMAXSIZE | LOB_MAX_SIZE [int] <read/write>
  The maximum size of data to be fetched from large objects (bytea or text fields). Objects with a bigger size are silently truncated. Default value is 1024.

• OBJECTMODE | OBJECT_MODE [1|0|Yes|No|True|False] <read/write>
  Enables fetching of arrays and composite types as Perl objects. Disabled by default.

    # connect with object mode enabled
    $con = DBE->connect( 'Driver=Pg;ObjectMode=1' );
    
    # disable object mode
    $con->attr_set( 'ObjectMode' => 0 );
    # do a query in text mode
    $res = $con->query( 'SELECT * FROM pg_namespace' );
    # enable object mode for further queries
    $con->attr_set( 'ObjectMode' => 1 );
  

  See Object Mode for more details.

• FETCHOBJECTNAMES | FETCH_OBJECT_NAMES [1|0|Yes|No|True|False] <read/write>
  Enable or disable fetching composite types as hash. Default value is Auto, which means that the fetch function you select decides about either fetching object names or not.

• OBJECTCACHEMAX | OBJECT_CACHE_MAX [int] <read/write>
  In object mode each type must be identified. The identification result is stored in the object cache for further faster access. This option sets the maximum size in bytes of the object cache. The limit may exceed since the cache must hold at least the objects for each active result set. Default value is 64 kibibyte.

• OBJECTCACHESIZE | OBJECT_CACHE_SIZE [int] <readonly>
  Returns the used size of the object cache.

    $cache_max = $con->attr_get( 'object_cache_max' );
    $cache_size = $con->attr_get( 'object_cache_size' );
    if( $cache_size > $cache_max ) {
        # add 64k to the object cache
        $con->attr_set( 'object_cache_max' => $cache_size + 65536 );
    }
  

• SEARCHPATH | SEARCH_PATH [string] <read/write>
  One or more schema names separated by comma to set as search path. Default is '"$user",public'.

• MESSAGES [string] <read/write>
  Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. DBE default is ERROR.

• BITASTEXT | BIT_AS_TEXT [1|0|Yes|No|True|False] <read/write>
  On true value bit and varbit types are returned in readable text format. Default value is true in standard text mode and false in object mode.

• BYTEAASTEXT | BYTEA_AS_TEXT [1|0|Yes|No|True|False] <read/write>
  On true value bytea types are returned in readable text format. Default value is true in standard text mode and false in object mode.

Connection Functions

$con -> text_to_bit ( $value )

Converts a text with '0' and '1' into a PostgreSQL compatible binary bit string.

Example

  $bit_string = $con->text_to_bit( '101101001' );

$con -> bit_to_text ( $value )

Converts a PostgreSQL binary bit string into a readable text representation.

$con -> unescape_bytea ( $value )

Converts a string representation of binary data into binary data.

$con -> escape_bytea ( $value )

Escapes binary data for use within an SQL command with the type bytea.

Object Mode

  # enable object mode
  $con->attr_set( 'object_mode' => 1 );
  # create a table with an array type
  $con->do(
      q/
      CREATE TABLE table1 (
          id serial primary key,
          names varchar(64)[]
      )
      /
  );
  # insert an array with two items
  $con->do( q/INSERT INTO table1 (names) VALUES('{"castle","heaven"}')/ );
  # select the row insert
  $res = $con->query( 'SELECT * FROM table1' );
  # fetch the row
  $res->fetch_row( \%row );
  # output:
  # %row = (
  #     'names' => [
  #         'castle',
  #         'heaven'
  #     ],
  #     'id' => 1
  # );

PL/pgSQL

  # the procedural language must be enabled once
  # $con->do( 'CREATE LANGUAGE plpgsql' );
  
  # create a table
  $con->do( 'CREATE TABLE temp (col1 INT, col2 INT, col3 VARCHAR(16))' );
  
  # create a PL/pgSQL block as function (we cannot execute it directly)
  $con->do(
      q~
      CREATE OR REPLACE FUNCTION temp() RETURNS BOOLEAN AS $$
      DECLARE
          x INTEGER := 100;
      BEGIN
          FOR i IN 1..10 LOOP
              IF MOD(i,2) = 0 THEN -- i is even
                  INSERT INTO temp VALUES (i, x, 'i is even');
              ELSE
                  INSERT INTO temp VALUES (i, x, 'i is odd');
              END IF;
              x := x + 100;
          END LOOP;
          RETURN TRUE;
      END;
      $$ LANGUAGE plpgsql;
      ~
  );
  
  # and execute it
  $con->do( 'SELECT * FROM temp()' );
  
  # show the result
  $res = $con->query( 'SELECT * FROM temp ORDER BY col1' );
  print $res->dump;
  
  # drop the function
  $con->do( 'DROP FUNCTION temp()' );
  
  # drop the table
  $con->do( 'DROP TABLE temp' );

Large Objects

$oid = $con -> lob_create ()

Creates a new large object. The return value is the OID that was assigned to the new large object, or undef on failure.

See lob_open_write() for an example.

$oid = $con -> lob_import ( $filename )

Imports an operating system file as a large object. $filename specifies the operating system name of the file to be imported as a large object. The return value is the OID that was assigned to the new large object, or undef on failure.

Example

  # -table layout-
  # CREATE TABLE pictures (title VARCHAR(16) PRIMARY KEY, data OID)
  
  # begin a transaction
  $con->begin_work();
  # prepare the statement
  $stmt = $con->prepare( 'INSERT INTO pictures (title,data) VALUES(?,?)' );
  # bind 'sweet_home' as title
  $stmt->bind_param( 1, 'sweet_home' );
  # import a file as large object
  $oid = $con->lob_import( '/path/to/sweet_home.png' );
  # bind the lob oid as data
  $stmt->bind_param( 2, $oid );
  # execute the statement
  $stmt->execute();
  # commit the transaction
  $con->commit();

$con -> lob_export ( $oid, $filename )

Exports a large object into an operating system file. The $oid argument specifies the OID of the large object to export and the filename argument specifies the operating system name of the file. Returns a true value on success, or undef on failure.

Example

  # -table layout-
  # CREATE TABLE pictures (title VARCHAR(16) PRIMARY KEY, data OID)
  
  # begin transaction
  $con->begin_work();
  # select the oid of picture 'sweet_home'
  ($oid) = $con->selectrow_array(
      "SELECT data FROM pictures WHERE title = 'sweet_home'" );
  # export large object
  $con->lob_export( $oid, '/tmp/sweet_home.png' );
  # close transaction
  $con->commit();

$lob = $con -> lob_open_write ( $oid )

Opens a large object in write (+read) mode. The $oid argument specifies the OID of the large object to open.

A documentation about the lob class functions can be found on the DBE manpage.

Example

  # -table layout-
  # CREATE TABLE pictures (title VARCHAR(16) PRIMARY KEY, data OID)
  
  # begin transaction
  $con->begin_work();
  # prepare the statement
  $stmt = $con->prepare( 'INSERT INTO pictures (title,data) VALUES(?,?)' );
  # bind 'sweet_home' to the 'title' parameter
  $stmt->bind_param( 1, 'sweet_home' );
  # create a large object
  $oid = $con->lob_create();
  # bind the lob oid to the 'data' parameter
  $stmt->bind_param( 2, $oid );
  # execute the statement
  $stmt->execute();
  # open lob for writing
  $lob = $con->lob_open_write( $oid );
  # write to the lob
  $lob->write( 'data of sweet home' );
  # close the lob
  $lob->close;
  # commit
  $con->commit();

$lob = $con -> lob_open_read ( $oid )

Opens a large object in read mode. The $oid argument specifies the OID of the large object to open.

A documentation about the lob class functions can be found on the DBE manpage.

Example

  # -table layout-
  # CREATE TABLE pictures (title VARCHAR(16) PRIMARY KEY, data OID)
  
  # begin transaction
  $con->begin_work();
  # get the oid to a large object
  ($oid) = $con->selectrow_array(
      "SELECT data FROM picture WHERE title = 'sweet_home'" );
  # open lob for reading
  $lob = $con->lob_open_read( $oid );
  # read from the lob
  while( ($got = $lob->read( $data, 1024 )) > 0 ) {
      print "got $got bytes\n";
  }
  # close the lob
  $lob->close;
  # close transaction
  $con->commit();

$con -> lob_unlink ( $oid )

Removes a large object. The $oid argument specifies the OID of the large object to remove. Returns a true value if successful, or undef on failure.

LIMITATIONS

KNOWN ERRORS

  # disable object mode
  $con->attr_set( 'object_mode' => 0 );
  
  # run multiple commands
  $con->do(
      q/
      DROP TABLE IF EXISTS temp;
      CREATE TABLE temp (id SERIAL, name VARCHAR(64));
      /
  );

AUTHORS

COPYRIGHT