From 25b0f313d56afe071a01ad4a7142615704a71e88 Mon Sep 17 00:00:00 2001 From: Michael Wallner Date: Fri, 26 Sep 2014 07:47:11 +0200 Subject: [PATCH] pq\Types docs --- pq/Types.md | 365 ++++++++++++++++++++++++++++++++++++++++ pq/Types/: Overview.md | 141 ++++++++++++++++ pq/Types/__construct.md | 16 ++ pq/Types/refresh.md | 15 ++ 4 files changed, 537 insertions(+) create mode 100644 pq/Types.md create mode 100644 pq/Types/: Overview.md create mode 100644 pq/Types/__construct.md create mode 100644 pq/Types/refresh.md diff --git a/pq/Types.md b/pq/Types.md new file mode 100644 index 0000000..1658a40 --- /dev/null +++ b/pq/Types.md @@ -0,0 +1,365 @@ +# 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. + +## Example: + + $val) { + printf("%-20s = %s\n", $key, is_bool($val) ? ($val?'t':'f') : $val); + } + + ?> + +Yields: + + oid = 22 + typname = int2vector + typnamespace = 11 + typowner = 10 + typlen = -1 + typbyval = f + typtype = b + typcategory = A + typispreferred = f + typisdefined = t + typdelim = , + typrelid = 0 + typelem = 21 + typarray = 1006 + typinput = int2vectorin + typoutput = int2vectorout + typreceive = int2vectorrecv + typsend = int2vectorsend + typmodin = - + typmodout = - + typanalyze = - + typalign = i + typstorage = p + typnotnull = f + typbasetype = 0 + typtypmod = -1 + typndims = 0 + typcollation = 0 + typdefaultbin = + typdefault = + typacl = + + +## Constants: + +* BOOL + OID of the `bool` type. +* BYTEA + OID of the `bytea` type. +* CHAR + OID of the `char` type. +* NAME + OID of the `name` type. +* INT8 + OID of the `int8` type. +* INT2 + OID of the `int2` type. +* INT2VECTOR + OID of the `int2vector` type. +* INT4 + OID of the `int4` type. +* REGPROC + OID of the `regproc` type. +* TEXT + OID of the `text` type. +* OID + OID of the `oid` type. +* TID + OID of the `tid` type. +* XID + OID of the `xid` type. +* CID + OID of the `cid` type. +* OIDVECTOR + OID of the `oidvector` type. +* PG_TYPE + OID of the `pg_type` type. +* PG_ATTRIBUTE + OID of the `pg_attribute` type. +* PG_PROC + OID of the `pg_proc` type. +* PG_CLASS + OID of the `pg_class` type. +* JSON + OID of the `json` type. +* XML + OID of the `xml` type. +* XMLARRAY + OID of the `xmlarray` type. +* JSONARRAY + OID of the `jsonarray` type. +* PG_NODE_TREE + OID of the `pg_node_tree` type. +* SMGR + OID of the `smgr` type. +* POINT + OID of the `point` type. +* LSEG + OID of the `lseg` type. +* PATH + OID of the `path` type. +* BOX + OID of the `box` type. +* POLYGON + OID of the `polygon` type. +* LINE + OID of the `line` type. +* LINEARRAY + OID of the `linearray` type. +* FLOAT4 + OID of the `float4` type. +* FLOAT8 + OID of the `float8` type. +* ABSTIME + OID of the `abstime` type. +* RELTIME + OID of the `reltime` type. +* TINTERVAL + OID of the `tinterval` type. +* UNKNOWN + OID of the `unknown` type. +* CIRCLE + OID of the `circle` type. +* CIRCLEARRAY + OID of the `circlearray` type. +* MONEY + OID of the `money` type. +* MONEYARRAY + OID of the `moneyarray` type. +* MACADDR + OID of the `macaddr` type. +* INET + OID of the `inet` type. +* CIDR + OID of the `cidr` type. +* BOOLARRAY + OID of the `boolarray` type. +* BYTEAARRAY + OID of the `byteaarray` type. +* CHARARRAY + OID of the `chararray` type. +* NAMEARRAY + OID of the `namearray` type. +* INT2ARRAY + OID of the `int2array` type. +* INT2VECTORARRAY + OID of the `int2vectorarray` type. +* INT4ARRAY + OID of the `int4array` type. +* REGPROCARRAY + OID of the `regprocarray` type. +* TEXTARRAY + OID of the `textarray` type. +* OIDARRAY + OID of the `oidarray` type. +* TIDARRAY + OID of the `tidarray` type. +* XIDARRAY + OID of the `xidarray` type. +* CIDARRAY + OID of the `cidarray` type. +* OIDVECTORARRAY + OID of the `oidvectorarray` type. +* BPCHARARRAY + OID of the `bpchararray` type. +* VARCHARARRAY + OID of the `varchararray` type. +* INT8ARRAY + OID of the `int8array` type. +* POINTARRAY + OID of the `pointarray` type. +* LSEGARRAY + OID of the `lsegarray` type. +* PATHARRAY + OID of the `patharray` type. +* BOXARRAY + OID of the `boxarray` type. +* FLOAT4ARRAY + OID of the `float4array` type. +* FLOAT8ARRAY + OID of the `float8array` type. +* ABSTIMEARRAY + OID of the `abstimearray` type. +* RELTIMEARRAY + OID of the `reltimearray` type. +* TINTERVALARRAY + OID of the `tintervalarray` type. +* POLYGONARRAY + OID of the `polygonarray` type. +* ACLITEM + OID of the `aclitem` type. +* ACLITEMARRAY + OID of the `aclitemarray` type. +* MACADDRARRAY + OID of the `macaddrarray` type. +* INETARRAY + OID of the `inetarray` type. +* CIDRARRAY + OID of the `cidrarray` type. +* CSTRINGARRAY + OID of the `cstringarray` type. +* BPCHAR + OID of the `bpchar` type. +* VARCHAR + OID of the `varchar` type. +* DATE + OID of the `date` type. +* TIME + OID of the `time` type. +* TIMESTAMP + OID of the `timestamp` type. +* TIMESTAMPARRAY + OID of the `timestamparray` type. +* DATEARRAY + OID of the `datearray` type. +* TIMEARRAY + OID of the `timearray` type. +* TIMESTAMPTZ + OID of the `timestamptz` type. +* TIMESTAMPTZARRAY + OID of the `timestamptzarray` type. +* INTERVAL + OID of the `interval` type. +* INTERVALARRAY + OID of the `intervalarray` type. +* NUMERICARRAY + OID of the `numericarray` type. +* TIMETZ + OID of the `timetz` type. +* TIMETZARRAY + OID of the `timetzarray` type. +* BIT + OID of the `bit` type. +* BITARRAY + OID of the `bitarray` type. +* VARBIT + OID of the `varbit` type. +* VARBITARRAY + OID of the `varbitarray` type. +* NUMERIC + OID of the `numeric` type. +* REFCURSOR + OID of the `refcursor` type. +* REFCURSORARRAY + OID of the `refcursorarray` type. +* REGPROCEDURE + OID of the `regprocedure` type. +* REGOPER + OID of the `regoper` type. +* REGOPERATOR + OID of the `regoperator` type. +* REGCLASS + OID of the `regclass` type. +* REGTYPE + OID of the `regtype` type. +* REGPROCEDUREARRAY + OID of the `regprocedurearray` type. +* REGOPERARRAY + OID of the `regoperarray` type. +* REGOPERATORARRAY + OID of the `regoperatorarray` type. +* REGCLASSARRAY + OID of the `regclassarray` type. +* REGTYPEARRAY + OID of the `regtypearray` type. +* UUID + OID of the `uuid` type. +* UUIDARRAY + OID of the `uuidarray` type. +* TSVECTOR + OID of the `tsvector` type. +* GTSVECTOR + OID of the `gtsvector` type. +* TSQUERY + OID of the `tsquery` type. +* REGCONFIG + OID of the `regconfig` type. +* REGDICTIONARY + OID of the `regdictionary` type. +* TSVECTORARRAY + OID of the `tsvectorarray` type. +* GTSVECTORARRAY + OID of the `gtsvectorarray` type. +* TSQUERYARRAY + OID of the `tsqueryarray` type. +* REGCONFIGARRAY + OID of the `regconfigarray` type. +* REGDICTIONARYARRAY + OID of the `regdictionaryarray` type. +* TXID_SNAPSHOT + OID of the `txid_snapshot` type. +* TXID_SNAPSHOTARRAY + OID of the `txid_snapshotarray` type. +* INT4RANGE + OID of the `int4range` type. +* INT4RANGEARRAY + OID of the `int4rangearray` type. +* NUMRANGE + OID of the `numrange` type. +* NUMRANGEARRAY + OID of the `numrangearray` type. +* TSRANGE + OID of the `tsrange` type. +* TSRANGEARRAY + OID of the `tsrangearray` type. +* TSTZRANGE + OID of the `tstzrange` type. +* TSTZRANGEARRAY + OID of the `tstzrangearray` type. +* DATERANGE + OID of the `daterange` type. +* DATERANGEARRAY + OID of the `daterangearray` type. +* INT8RANGE + OID of the `int8range` type. +* INT8RANGEARRAY + OID of the `int8rangearray` type. +* RECORD + OID of the `record` type. +* RECORDARRAY + OID of the `recordarray` type. +* CSTRING + OID of the `cstring` type. +* ANY + OID of the `any` type. +* ANYARRAY + OID of the `anyarray` type. +* VOID + OID of the `void` type. +* TRIGGER + OID of the `trigger` type. +* EVENT_TRIGGER + OID of the `event_trigger` type. +* LANGUAGE_HANDLER + OID of the `language_handler` type. +* INTERNAL + OID of the `internal` type. +* OPAQUE + OID of the `opaque` type. +* ANYELEMENT + OID of the `anyelement` type. +* ANYNONARRAY + OID of the `anynonarray` type. +* ANYENUM + OID of the `anyenum` type. +* FDW_HANDLER + OID of the `fdw_handler` type. +* ANYRANGE + OID of the `anyrange` type. + +## Properties: + +* public (readonly) pq\Connection $connection + The connection which was used to obtain type information. diff --git a/pq/Types/: Overview.md b/pq/Types/: Overview.md new file mode 100644 index 0000000..5f190a7 --- /dev/null +++ b/pq/Types/: Overview.md @@ -0,0 +1,141 @@ +# 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 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 +oid, $text->typname); +``` + +> ***NOTE:*** +> The pg_type relation fields have a ``typ`` prefix. + +## Usage when [executing queries](QueryExecution) ## + +``` +#!php +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 ## + +When ext-pq has been built with PostgreSQL server headers (*) present, then ```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)); +``` + +> ***NOTE:*** +> You can test for static type support with ```pq\Types::DEFINED === true``` + +(*) ```include/postgresql/server/catalog/pg_type.h``` + +### pq\Datetime ### +With static type support, 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); + +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 ## + +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: + +``` +#!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,"; + } + } + 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); + +print_r($data); + +``` + +Output would be the following: +``` +Array +( + [foo] => bar +) +``` diff --git a/pq/Types/__construct.md b/pq/Types/__construct.md new file mode 100644 index 0000000..9ed87f2 --- /dev/null +++ b/pq/Types/__construct.md @@ -0,0 +1,16 @@ +# void pq\Types::__construct(pq\Connection $conn[, array $namespaces = NULL) + +Create a new instance populated with information obtained from the `pg_type` relation. + +## Params: + +* pq\Connection $conn + The connection to use. +* Optional array $namespaces = NULL + Which namespaces to query (defaults to `public` and `pg_catalog`). + +## Throws: + +* pq\Exception\InvalidArgumentException +* pq\Exception\BadMethodCallException +* pq\Exception\RuntimeException diff --git a/pq/Types/refresh.md b/pq/Types/refresh.md new file mode 100644 index 0000000..ebb3560 --- /dev/null +++ b/pq/Types/refresh.md @@ -0,0 +1,15 @@ +# void pq\Types::refresh([array $namespaces = NULL]) + +Refresh type information from `pg_type`. + +## Params: + +* Optional array $namespaces = NULL + Which namespaces to query (defaults to `public` and `pg_catalog`). + +## Throws: + +* pq\Exception\InvalidArgumentException +* pq\Exception\BadMethodCallException +* pq\Exception\RuntimeException + -- 2.30.2