DBIx::PgLink::Local - DBI emulation for local data access in PostgreSQL PL/Perl function
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' ... $$
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.
pg_dbh
Returns singleton instance of class DBIx::PgLink::Local. Exported by default.
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.
spi_prepare()
PREPARE
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.
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.
do
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.
$query
execute
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.
$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.
fetchrow_hashref
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.
fetchall_arrayref
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().
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.
$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.
spi_exec_prepared
spi_query_prepared
$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.
undef
fetchrow_array
$scalar = $q->fetchrow_array;
Fetches the next row of data and return one field value.
$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.
spi_cursor_close
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.
array_nulls
Lot ot this module code will be obsolete when (and if) DBD::PgSPI starts support real prepared statements.
DBI, DBD::Pg, Tie::Cache::LRU, PostgreSQL Manual
Alexey Sharafutdinov <alexey.s.v.br@gmail.com>
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install DBIx::PgLink, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::PgLink
CPAN shell
perl -MCPAN -e shell install DBIx::PgLink
For more information on module installation, please visit the detailed CPAN module installation guide.