From fec54590ede47fa78885e01713a15622064c1858 Mon Sep 17 00:00:00 2001 From: Michael Wallner Date: Wed, 1 Oct 2014 16:36:13 +0200 Subject: [PATCH] fiy pq\Types overview; fix some links --- pq.md | 2 +- pq/Connection/: Executing Queries.md | 8 +- pq/Result/: Fetching Results.md | 2 +- pq/Statement.md | 1 + pq/Types.md | 6 +- pq/Types/: Overview.md | 206 +++++++++++++-------------- 6 files changed, 110 insertions(+), 115 deletions(-) diff --git a/pq.md b/pq.md index 9e920d4..50221d1 100644 --- a/pq.md +++ b/pq.md @@ -13,7 +13,7 @@ This is a modern binding to the mature [libpq](http://www.postgresql.org/docs/cu ## Installation: -This extension is hosted at PECL and can be installed with PEAR’s pecl command: +This extension is hosted at [PECL](http://pecl.php.net) and can be installed with [PEAR](http://pear.php.net)'s pecl command: # pecl install pq diff --git a/pq/Connection/: Executing Queries.md b/pq/Connection/: Executing Queries.md index f1983a9..81a16e9 100644 --- a/pq/Connection/: Executing Queries.md +++ b/pq/Connection/: Executing Queries.md @@ -30,7 +30,7 @@ pq\Connection::execParams() accepts a ***query string*** with a single SQL query The second argument is an ***array of parameters*** to execute the prepared statement with. -If the third argument is present, an ***array with pg_type OIDs***, those types will be used for the parameters. See [Using types](pq/: Using Types) for details. +If the third argument is present, an ***array with pg_type OIDs***, those types will be used for the parameters. See [Using types](pq/Types/: Overview) for details. execParams("SELECT int($1) * s, int($2) * s, int($3) * s FROM generate_series(1,3) s", array(1,2,3)); @@ -41,11 +41,11 @@ An object of class pq\Result is returned on success. See [Fetching results](pq/R ## Explicit prepare & execute of a named statement -pq\Connection::prepare() requires the ***statement name*** as string as first argument. This name is used later to refer to this prepared statement. See [Prepared statements](pq/: Prepared Statements) for details. +pq\Connection::prepare() requires the ***statement name*** as string as first argument. This name is used later to refer to this prepared statement. See [Prepared statements](pq/Statement) for details. The second argument is a ***query string*** containing a single SQL query, which will be prepared on the server. -If the third argument is present, an ***array with pg_type OIDs***, those types will be used for the parameters. See [Using types](pq/: Using Types) for details. +If the third argument is present, an ***array with pg_type OIDs***, those types will be used for the parameters. See [Using types](pq/Types/: Overview) for details. -An object of class pq\Statement will be returned on success. See [Prepared statements](pq/: Prepared Statements) for details. +An object of class pq\Statement will be returned on success. See [Prepared statements](pq/Statement) for details. diff --git a/pq/Result/: Fetching Results.md b/pq/Result/: Fetching Results.md index b8dd77e..15941e4 100644 --- a/pq/Result/: Fetching Results.md +++ b/pq/Result/: Fetching Results.md @@ -1,6 +1,6 @@ # pq\Result: Overview -An synchronous pq\Connection::exec*() call, or calls to pq\Connection::getResult() after using [asynchronous queries](pq/Connection/: Asynchronous Usage) returns an instance of pq\Result on success. See [Query execution](pq/Connection/: Executing Queries), optionally with [types](pq/Types/: Overview) and [Prepared Statements](pq/Statement/: Overview) for details about how to execute queries. +An synchronous pq\Connection::exec*() call, or calls to pq\Connection::getResult() after using [asynchronous queries](pq/Connection/: Asynchronous Usage) returns an instance of pq\Result on success. See [Query execution](pq/Connection/: Executing Queries), optionally with [types](pq/Types/: Overview) and [Prepared Statements](pq/Statement) for details about how to execute queries. ## Fetch types: diff --git a/pq/Statement.md b/pq/Statement.md index 764003f..65b10a6 100644 --- a/pq/Statement.md +++ b/pq/Statement.md @@ -1,6 +1,7 @@ # class pq\Statement A named prepared statement. +See pq\Connection::prepare(). ## Properties: diff --git a/pq/Types.md b/pq/Types.md index 1658a40..bb4fc4a 100644 --- a/pq/Types.md +++ b/pq/Types.md @@ -1,9 +1,7 @@ # class pq\Types implements ArrayAccess Accessor to the PostgreSQL `pg_type` relation. - -> ***NOTE:*** - The following OID constants are obtained from stock PostgreSQL 9.3.5. Types from f.e. extensions can be accessed through the ArrayAccess interface of pq\Types. +See [here for an overview](pq/Types/: Overview). ## Example: @@ -51,6 +49,8 @@ Yields: typdefault = typacl = +> ***NOTE:*** + The following OID constants are obtained from stock PostgreSQL 9.3.5. Types from f.e. extensions can be accessed through the ArrayAccess interface of pq\Types. ## Constants: diff --git a/pq/Types/: Overview.md b/pq/Types/: Overview.md index 5f190a7..023704d 100644 --- a/pq/Types/: Overview.md +++ b/pq/Types/: Overview.md @@ -1,141 +1,135 @@ -# pq\Types AKA pg_type # +# pq\Types AKA pg_type -The ```pq\Types``` class provides an easy interface to access information stored in PostgreSQL's pg_type relation, f.e. type OIDs and names. +The pq\Types class provides an easy interface to access information stored in PostgreSQL's pg_type relation, f.e. type OIDs and names. -The first argument to the ```pq\Types``` constructor must be an ***instance*** of ```pq\Connection```. -An ***optional array of namespace names*** is expected as second argument, where 'public' and 'pg_catalog' are queried by default if no namespaces are specified. +The first argument to the pq\Types constructor must be an instance of pq\Connection. +An optional array of namespace names is expected as second argument, where 'public' and 'pg_catalog' are queried by default if no namespaces are specified. -``` -#!php - The types are standard objects indexed by OID and by name and accessible by array key/index: -``` -#!php -oid, $text->typname); -``` + oid, $text->typname); + + ?> -> ***NOTE:*** -> The pg_type relation fields have a ``typ`` prefix. +> ***NOTE:*** + The pg_type relation fields have a ``typ`` prefix. -## Usage when [executing queries](QueryExecution) ## +## Usage when [executing queries](pq/Connection/: Executing Queries) -``` -#!php -execParams("SELECT \$1 + \$2", array(10, 20), - array($types["int4"]->oid, $types["int4"]->oid)); -``` + + execParams( + "SELECT \$1 + \$2", + array(10, 20), + array($types["int4"]->oid, $types["int4"]->oid)); + + ?> You can pass a type OID for each parameter of a pepared statement. The PostgreSQL server will try to infer a type by context for any parameters for which no type OID was specified. -## Static types ## +## Static types -When ext-pq has been built with PostgreSQL server headers (*) present, then ```pq\Types``` has class constants for the standard types' oids already defined. +pq\Types has class constants for the standard types' OIDs already defined. -``` -#!php -execParams("SELECT \$1 + \$2", array(10, 20), - array(pq\Types::INT4, pq\Types::INT4)); -``` + execParams("SELECT \$1 + \$2", + array(10, 20), + array(pq\Types::INT4, pq\Types::INT4)); -> ***NOTE:*** -> You can test for static type support with ```pq\Types::DEFINED === true``` -(*) ```include/postgresql/server/catalog/pg_type.h``` +### pq\Datetime -### pq\Datetime ### -With static type support, date(time) values will automatically be converted to ```pq\Datetime``` objects. +Date/time values will automatically be converted to pq\Datetime objects. -``` -#!php -exec(" - SELECT - NOW()::date - ,NOW()::abstime - ,NOW()::timestamp - ,NOW()::timestamptz -")->fetchRow(pq\Result::FETCH_ARRAY); + $conn = new pq\Connection(); + $data = $conn->exec(" + SELECT + NOW()::date + ,NOW()::abstime + ,NOW()::timestamp + ,NOW()::timestamptz + ")->fetchRow(pq\Result::FETCH_ARRAY); -foreach ($data as $datetime) { - printf("%-40s (%s)\n", $datetime, $datetime->format); -} -``` + foreach ($data as $datetime) { + printf("%-40s (%s)\n", $datetime, $datetime->format); + } + + ?> Outputs: -``` -2013-09-20 (Y-m-d) -2013-09-20 14:34:38 (Y-m-d H:i:s) -2013-09-20 14:34:38.494886 (Y-m-d H:i:s.u) -2013-09-20 14:34:38.494886+0200 (Y-m-d H:i:s.uO) -``` -## Custom converters ## + 2013-09-20 (Y-m-d) + 2013-09-20 14:34:38 (Y-m-d H:i:s) + 2013-09-20 14:34:38.494886 (Y-m-d H:i:s.u) + 2013-09-20 14:34:38.494886+0200 (Y-m-d H:i:s.uO) -ext-pq provides an interface called ```pq\Converter``` which one can implement to perform custom type conversions in a transparent manner. Consider the following naive example converting ```HSTORE``` columns on the fly: +## Custom converters -``` -#!php -oid = $types["hstore"]->oid; - } - - function convertTypes() { - return [$this->oid]; - } - - function convertFromString($string) { - return eval("return [$string];"); - } - - function convertToString($data) { - $string = ""; - foreach ($data as $k => $v) { - $string .= "\"".addslashes($k)."\"=>"; - if (isset($v)) { - $string .= "\"".addslashes($v)."\","; - } else { - $string .= "NULL,"; + oid = $types["hstore"]->oid; + } + + function convertTypes() { + return [$this->oid]; + } + + function convertFromString($string, $type) { + return eval("return [$string];"); + } + + function convertToString($data, $type) { + $string = ""; + foreach ($data as $k => $v) { + $string .= "\"".addslashes($k)."\"=>"; + if (isset($v)) { + $string .= "\"".addslashes($v)."\","; + } else { + $string .= "NULL,"; + } } + return $string; } - return $string; } -} -$conn = new pq\Connection(); -$type = new pq\Types($conn); -$conv = new Hstore($type); -$conn->setConverter($conv); -$conn->exec("SELECT '\"foo\"=>\"bar\"'::hstore hs")->fetchCol(0, $data); + $conn = new pq\Connection(); + $type = new pq\Types($conn); + $conv = new Hstore($type); + $conn->setConverter($conv); + $conn->exec("SELECT '\"foo\"=>\"bar\"'::hstore hs")->fetchCol(0, $data); -print_r($data); + print_r($data); -``` + ?> Output would be the following: -``` -Array -( - [foo] => bar -) -``` + + Array + ( + [foo] => bar + ) -- 2.30.2