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