The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-PgLink-0.01
River stage zero No dependents
++
/ DBIx::PgLink::Local

NAME

DBIx::PgLink::Local - DBI emulation for local data access in PostgreSQL PL/Perl function

SYNOPSIS

PostgreSQL script

  CREATE FUNCTION fn() RETURNS ... LANGUAGE plperlu AS $$

    ...

    use DBIx::PgLink::Local;

    $q = pg_dbh->prepare( q<SELECT 'Hello, ' || ? as foo> );
    $q->execute("world");
    while (my $row = $q->fetchrow_hashref) {
      elog 'INFO', $row->{foo}; # prints 'Hello, world'
    }

    ...

    $v = pg_dbh->selectrow_array( 
      'SELECT $1 * $1 as bar',  # query string
      { types=>['INT4'] } ),    # attributes
      3                         # parameter values
    ); 
    elog 'INFO', $v; # prints '9'

    ...

  $$

DESCRIPTION

WARNING: this module works only in PostgreSQL functions written in PL/PerlU language in PostgreSQL server version 8.2 or higher.

DBIx::PgLink::Local is a wrapper around PL/Perl Server Programming Interface (SPI) functions. Module provides only basic functions of DBI. For full DBI-compatible driver look at DBD::PgSPI.

Module manage prepared statements and cache query plans. It is not depend on other DBIx::PgLink code (except DBIx::PgLink::Logger) and can be used in any PL/Perl function.

SUBROUTINES

pg_dbh

Returns singleton instance of class DBIx::PgLink::Local. Exported by default.

METHODS

quote
  $sql = pg_dbh->quote($value);

Quote a string literal for use as a literal value in an SQL statement, by escaping single quote and backslash characters and adding the single quotes.

quote_identifier
  $sql = pg_dbh->quote_identifier( $name );
  $sql = pg_dbh->quote_identifier( $schema, $object );

Quote an identifier (table name etc.) for use in an SQL statement, by escaping double quote and adding double quotes.

prepare
    $sth = pg_dbh->prepare($statement);
    $sth = pg_dbh->prepare($statement, \%attr);

Prepares a statement for later execution by the database engine and returns a reference to a statement handle. Statement handle is object containing query plan.

Supports $n ("dollar sign numbers") and ? (question mark) placeholder styles. $n-style is PostgreSQL default and preferred over quotation marks.

Wrapped spi_prepare() function cannot infer parameter data type from the context, although SQL command PREPARE can. If no parameter types specified, prepare implicitly detect placeholders and assign 'TEXT' type to all of them.

prepare attributes:

types

Supply explicit data type names for parameters in types attribute as array-ref:

    $sth = pg_dbh->prepare(
      'SELECT * FROM foo WHERE bar=$1 and baz=$2', 
      { types => [qw/TEXT INT4/] }
    );

Type names are case insensitive. Examples: 'TEXT', 'INT4', 'INT8', 'FLOAT4', 'FLOAT8'. In addition 'int', 'integer' are aliased to 'INT4', 'double' to 'FLOAT8'.

Only "dollar sign number" placeholders can be used with explicit types.

See alse "Placeholders" in DBD::Pg.

boolean

Array-ref containing field names in result set with boolean type. Converts PostgreSQL boolean values to Perl ('f' -> 0, 't' -> 1).

Also accepted hashref with field name as key.

array

Array-ref containing field names in result set with array type. Converts PostgreSQL array values to Perl array.

Also accepted hashref with field name as key.

no_cursor

Boolean: do not create cursor and fetch all data at once. Automatically set for any not SELECT/INSERT/UPDATE/DELETE query.

no_cache

Boolean: do not save query plan. Automatically set for any not SELECT/INSERT/UPDATE/DELETE query.

no_parse

Boolean: make no attempt to find placeholders in query and replace '?' marks. Automatically set for do method with no parameter values.

prepare_cached
    $sth = pg_dbh->prepare_cached($statement);
    $sth = pg_dbh->prepare_cached($statement, \%attr);

Like "prepare" except that the plan for statement will be stored in a global (session) hash. If another call is made to prepare_cached with the same $query value, then the corresponding cached plan will be used. Statement handles are not cached, it is safe to mix different prepare_cached and execute with the same query string.

Cache is managed by LRU algorithm. Default cache size is 100. Cache size can be configured via PostgreSQL run-time parameter plperl.plan_cache_size. See Customized Options in PostgreSQL Manual for example how to enable plperl custom variable class.

do
  $rows = pg_dbh->do($statement)
  $rows = pg_dbh->do($statement, \%attr)
  $rows = pg_dbh->do($statement, \%attr, @bind_values)

Prepare and execute a single statement. Returns the number of rows affected. Plan is cached.

selectrow_array
  $scalar = pg_dbh->selectall_arrayref($statement)
  $scalar = pg_dbh->selectall_arrayref($statement, \%attr)
  $scalar = pg_dbh->selectall_arrayref($statement, \%attr, @bind_values)

This utility method combines prepare_cached, execute and fetchrow_hashref into a single call. In scalar context returns single value from first row of resultset. If called for a statement handle that has more than one column, it is undefined whether column will be return.

NOTE: in list context always dies, because of internal limitation.

selectrow_hashref
  $hash_ref = $dbh->selectrow_hashref($statement);
  $hash_ref = $dbh->selectrow_hashref($statement, \%attr);
  $hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);

This utility method combines prepare_cached, execute and fetchrow_hashref into a single call. It returns the first row of data from the statement.

selectall_arrayref
  $ary_ref = pg_dbh->selectall_arrayref($statement)
  $ary_ref = pg_dbh->selectall_arrayref($statement, \%attr)
  $ary_ref = pg_dbh->selectall_arrayref($statement, \%attr, @bind_values)

This utility method combines prepare_cached, execute and fetchall_arrayref into a single call. It returns a reference to an array containing a reference to a hash for each row of data fetched.

Note that unlike DBI selectall_arrayref returns arrayref of hashes.

selectall_hashref
  $hash_ref = pg_dbh->selectall_hashref($statement, $key_field)
  $hash_ref = pg_dbh->selectall_hashref($statement, $key_field, \%attr)
  $hash_ref = pg_dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values)

This utility method combines prepare_cached, execute and fetchrow_hashref into a single call. It returns a reference to a hash containing one entry, at most, for each row, as returned by fetchall_hashref().

PostgreSQL-only methods

pg_flush_plan_cache
  pg_dbh->pg_flush_plan_cache;
  pg_dbh->pg_flush_plan_cache($regex);

Free all or selected prepared query plans from cache. Use after changing of database schema.

pg_to_perl_array
  @arr = pg_dbh->pg_to_perl_array('{1,2,3}');

Convert text representation of PostgreSQL array to Perl array.

pg_from_perl_array
  $string = pg_dbh->pg_from_perl_array(1,2,3,undef,'hello'); 
  # returns '{"1","2","3",NULL,"hello"}'

Convert Perl array to PostgreSQL array literal.

pg_to_perl_hash
  $hashref = pg_dbh->pg_to_perl_hash('{foo,1,bar,2}');

Convert text representation of PostgreSQL array to Perl hash.

This method is particularly useful for PL/Perl array argument conversion, for PL/Perl stringify it.

pg_from_perl_hash
  $string = pg_dbh->pg_from_perl_hash({foo=>1,bar=>2}); 
  # returns '{foo,1,bar,2}'

Convert Perl hash reference to PostgreSQL array literal.

pg_to_perl_encoding

Convert name of PostgreSQL encoding to Perl encoding name. See Encode.

pg_server_version

Indicates which version of local PostgreSQL that hosts PL/Perl function. Returns a number with major, minor, and revision together; version 8.2.5 would be 80205

pg_current_database

Returns name of local database PostgreSQL that hosts PL/Perl function.

pg_session_user

Returns PostgreSQL session user name. See System Information Functions chapter of PostgreSQL Manual.

STATEMENT METHODS

execute
  $q->execute;
  $q->execute(@values);

Execute prepared statement.

When statement prepared with true value of no_cursor attribute, all rows are fetched at once (if it is data retrieving operation) and execute returns number of proceeded rows.

When attribute no_cursor is not set, execute open cursor and fetch row-by-row. In this mode method always returns -1 because number of affected rows can not be known.

Wrapper of spi_exec_prepared / spi_query_prepared.

fetchrow_hashref
  $hash_ref = $q->fetchrow_hashref;

Fetches the next row of data and returns a reference to an hash holding the field values. If there are no more rows or if an error occurs, then fetchrow_hashref returns an undef.

fetchrow_array
  $scalar = $q->fetchrow_array;

Fetches the next row of data and return one field value.

NOTE: in list context always dies, because of internal limitation.

fetchall_arrayref
  $row_aref = $q->fetchall_arrayref;

The method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to an array that contains one reference per row. Note that unlike DBI fetchall_arrayref returns arrayref of hashes.

finish
  $q->finish;

Indicate that no more data will be fetched from this statement handle before it is either executed again or destroyed.

Wrapper of spi_cursor_close.

CAVEATS

  • SQL parsing for parameters in prepare is dumb.

    Use explicit types if query contains string like '$1' or '?' in literal, identifier or comment.

  • Full set of selectXXX and fetchXXX methods is not implemented.

    In PL/Perl data access layer every data row (tuple) converted to hash, and there is no easy way to restore original column order.

  • selectall_arrayref and fetchall_arrayref always returns reference to array of hashes

  • selectrow_array and fetchrow_array works in scalar context only.

  • Data fetching slower than PL/PGSQL.

    The tuple->hash conversion take extra time and memory.

  • No automatic plan invalidation.

    Use pg_flush_plan_cache (or reconnect) after database schema changes.

  • Array conversion suppose that array_nulls variable is ON.

  • Lot ot this module code will be obsolete when (and if) DBD::PgSPI starts support real prepared statements.

SEE ALSO

DBI, DBD::Pg, Tie::Cache::LRU, PostgreSQL Manual

AUTHOR

Alexey Sharafutdinov <alexey.s.v.br@gmail.com>

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.