git.m6w6.name Git - mdref/mdref-pq/blob - pq/Types/: Overview.md

   1 # pq\Types AKA pg_type #
   2
   3 The ```pq\Types``` class provides an easy interface to access information stored in PostgreSQL's pg_type relation, f.e. type OIDs and names.
   4
   5
   6 The first argument to the ```pq\Types``` constructor must be an ***instance*** of ```pq\Connection```.
   7 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.
   8
   9 ```
  10 #!php
  11 <?php
  12 $types = new pq\Types(new pq\Connection);
  13 ```
  14
  15 The types are standard objects indexed by OID and by name and accessible by array key/index:
  16 ```
  17 #!php
  18 <?php
  19 $text = $types["text"];
  20 $text = $types[25];
  21
  22 printf("TEXT type (oid=%d, name=%s)\n", $text->oid, $text->typname);
  23 ```
  24
  25 > ***NOTE:***
  26 > The pg_type relation fields have a ``typ`` prefix.
  27
  28 ## Usage when [executing queries](QueryExecution) ##
  29
  30 ```
  31 #!php
  32 <?php
  33 $connection = new pq\Connection;
  34 $types = new pq\Types($connection);
  35 $result = $connection->execParams("SELECT \$1 + \$2", array(10, 20),
  36   array($types["int4"]->oid, $types["int4"]->oid));
  37 ```
  38
  39 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.
  40
  41 ## Static types ##
  42
  43 When ext-pq has been built with PostgreSQL server headers (*) present, then ```pq\Types``` has class constants for the standard types' oids already defined.
  44
  45 ```
  46 #!php
  47 <?php
  48 $connection = new pq\Connection;
  49 $result = $connection->execParams("SELECT \$1 + \$2", array(10, 20),
  50   array(pq\Types::INT4, pq\Types::INT4));
  51 ```
  52
  53 > ***NOTE:***
  54 > You can test for static type support with ```pq\Types::DEFINED === true```
  55
  56 (*) ```include/postgresql/server/catalog/pg_type.h```
  57
  58 ### pq\Datetime ###
  59 With static type support, date(time) values will automatically be converted to ```pq\Datetime``` objects.
  60
  61 ```
  62 #!php
  63 <?php
  64
  65 $conn = new pq\Connection();
  66 $data = $conn->exec("
  67     SELECT
  68          NOW()::date
  69         ,NOW()::abstime
  70         ,NOW()::timestamp
  71         ,NOW()::timestamptz
  72 ")->fetchRow(pq\Result::FETCH_ARRAY);
  73
  74 foreach ($data as $datetime) {
  75     printf("%-40s (%s)\n", $datetime, $datetime->format);
  76 }
  77 ```
  78
  79 Outputs:
  80 ```
  81 2013-09-20                               (Y-m-d)
  82 2013-09-20 14:34:38                      (Y-m-d H:i:s)
  83 2013-09-20 14:34:38.494886               (Y-m-d H:i:s.u)
  84 2013-09-20 14:34:38.494886+0200          (Y-m-d H:i:s.uO)
  85 ```
  86
  87 ## Custom converters ##
  88
  89 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:
  90
  91 ```
  92 #!php
  93 <?php
  94
  95 class Hstore implements pq\Converter
  96 {
  97         private $oid;
  98
  99         function __construct(pq\Types $types) {
 100                 $this->oid = $types["hstore"]->oid;
 101         }
 102
 103         function convertTypes() {
 104                 return [$this->oid];
 105         }
 106
 107         function convertFromString($string) {
 108                 return eval("return [$string];");
 109         }
 110
 111         function convertToString($data) {
 112                 $string = "";
 113                 foreach ($data as $k => $v) {
 114                         $string .= "\"".addslashes($k)."\"=>";
 115                         if (isset($v)) {
 116                                 $string .= "\"".addslashes($v)."\",";
 117                         } else {
 118                                 $string .= "NULL,";
 119                         }
 120                 }
 121                 return $string;
 122         }
 123 }
 124
 125 $conn = new pq\Connection();
 126 $type = new pq\Types($conn);
 127 $conv = new Hstore($type);
 128 $conn->setConverter($conv);
 129 $conn->exec("SELECT '\"foo\"=>\"bar\"'::hstore hs")->fetchCol(0, $data);
 130
 131 print_r($data);
 132
 133 ```
 134
 135 Output would be the following:
 136 ```
 137 Array
 138 (
 139     [foo] => bar
 140 )
 141 ```