man SnowPg () - A simple wrapper class for Pg.

NAME

SnowPg - A simple wrapper class for Pg.

SYNOPSIS

use SnowPg;

Connecting to the database

# Connect to the database and don't kill the connection even when CW$db goes # out of scope. If a connection is already found open matching supplied # connection string, it will be used (with a rollback if necessary)

        $db = SnowPg->new(connect_string => 'dbname=abc user=123',
                persist => 0,           # don't re-use connections 
                debug => 1,             # send debug info to STDERR
                debugfile => '/var/log/db.log',         # append debug info to db.log
        );

Performing queries

        $db->query( q[SELECT %q AS Quoted], 'quoted escaped string' );

        # %q    => quoted and escaped
        #       eg. bl'ah -> 'bl\'ah'
        #           undef -> NULL

        # %l    => quoted list of strings
        #       eg. [ 'one', 'two' ]  -> 'one','two'
        #           [ @list ] ->  'abc','def'
        #               etc..

        # %u    => unquoted and unescaped (e.g. for numerical)
        #       eg. 12345 -> 12345
        #           undef -> NULL

        # %n    => boolean evaluated to NULL or NOT NULL
        #       eg. 0 or empty set or undef -> NULL
        #           1 -> NOT NULL

        # %b    => boolean evaluated to 't' or 'f'
        #       eg. 1 -> 't'
        #           0 -> 'f'
        #           undef -> NULL

        # %x    => bytea escaping (see below)

Fetching query results

# Number of result rows

        $n = $db->ntuples();

# Get a single row of results

        if (%result = $db->fetchrow) {
                $quoted = $result{quoted};
        }

# Get multiple rows of results

        # Spelling it out
                while (%res = $db->fetchrow()) {
                        foreach $k (keys %res) {
                                print "$k = $res{$k}\n";
                        }
                }

        # Getting a single column of rows into a list

                @values = $db->fetchlist('fieldname');

        # Getting all rows into a list of hashes

                @rows = $db->fetchlist();
                # access example:
                print "Result is " . $rows[5]->{columnname};

        # Getting two columns into a hash

                %values = $db->fetchhash('fieldname1' => 'fieldname2');

        # Getting multiple columns into a hash using any field as a key

                %values = $db->fetchhash('fieldname1');
                # access example:
                foreach $key (keys %values) {
                        $field2 = $values{$key}->{fieldname2};
                        $field3 = $values{$key}->{fieldname3};
                        print "Row: $key has field2=$field2 and field3=$field3\n";
                }
                # NOTE: specifying a non-unique field will cause older rows
                # to be replaced with new rows in the order they are
                # received from the server!

Transactions

        $db->begin();

        $db->commit();

        $db->rollback();
        # Rollback gets called automatically if object is destroyed. 
        # (Connection may be persistent)

Byte array handling (BYTEA)

        Simply send SnowPG the byte array as if it was a normal variable using
        query() and %q, eg:

        $db->query( q[INSERT INTO blobtable (blobcol) VALUES (%x)], $bytes );

        To decode postgres reponse, use:

        $bytes = SnowPg->unbytea( $postgres_output );

PostgreSQL signalling

        # (see Pg for more information)
        # To get the socket fd:

                $sock = $db->socket();

        # To get notifications:

                @notes = $db->notifies();

        # To consume input

                $db->consume();

CONTACT DETAILS

Author: Andrew Snow, andrew@modulus.org