3 @version v5.20.19 13-Dec-2020
4 @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
5 @copyright (c) 2014 Damien Regad, Mark Newnham and the ADOdb community
6 Released under both BSD license and Lesser GPL library license.
7 Whenever there is any discrepancy between the two licenses,
8 the BSD license will take precedence.
11 Original version derived from Alberto Cerezal (acerezalp@dbnet.es) - DBNet Informatica & Comunicaciones.
12 08 Nov 2000 jlim - Minor corrections, removing mysql stuff
13 09 Nov 2000 jlim - added insertid support suggested by "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
14 jlim - changed concat operator to || and data types to MetaType to match documented pgsql types
15 see http://www.postgresql.org/devel-corner/docs/postgres/datatype.htm
16 22 Nov 2000 jlim - added changes to FetchField() and MetaTables() contributed by "raser" <raser@mail.zen.com.tw>
17 27 Nov 2000 jlim - added changes to _connect/_pconnect from ideas by "Lennie" <leen@wirehub.nl>
18 15 Dec 2000 jlim - added changes suggested by Additional code changes by "Eric G. Werk" egw@netguide.dk.
19 31 Jan 2002 jlim - finally installed postgresql. testing
20 01 Mar 2001 jlim - Freek Dijkstra changes, also support for text type
22 See http://www.varlena.com/varlena/GeneralBits/47.php
24 -- What indexes are on my table?
25 select * from pg_indexes where tablename = 'tablename';
27 -- What triggers are on my table?
28 select c.relname as "Table", t.tgname as "Trigger Name",
29 t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
30 t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
31 p.proname as "Function Name"
32 from pg_trigger t, pg_class c, pg_class cc, pg_proc p
33 where t.tgfoid = p.oid and t.tgrelid = c.oid
34 and t.tgconstrrelid = cc.oid
35 and c.relname = 'tablename';
37 -- What constraints are on my table?
38 select r.relname as "Table", c.conname as "Constraint Name",
39 contype as "Constraint Type", conkey as "Key Columns",
40 confkey as "Foreign Columns", consrc as "Source"
41 from pg_class r, pg_constraint c
42 where r.oid = c.conrelid
43 and relname = 'tablename';
47 // security - hide paths
48 if (!defined('ADODB_DIR')) die();
50 function adodb_addslashes($s)
53 if ($len == 0) return "''";
54 if (strncmp($s,"'",1) === 0 && substr($s,$len-1) == "'") return $s; // already quoted
56 return "'".addslashes($s)."'";
59 class ADODB_postgres64 extends ADOConnection{
60 var $databaseType = 'postgres64';
61 var $dataProvider = 'postgres';
62 var $hasInsertID = true;
63 var $_resultid = false;
64 var $concat_operator='||';
65 var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
66 var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
67 and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
68 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
70 select viewname,'V' from pg_views where viewname not like 'pg\_%'";
71 //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
72 var $isoDates = true; // accepts dates in ISO format
73 var $sysDate = "CURRENT_DATE";
74 var $sysTimeStamp = "CURRENT_TIMESTAMP";
75 var $blobEncodeType = 'C';
76 var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
77 FROM pg_class c, pg_attribute a,pg_type t
78 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
79 AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
81 // used when schema defined
82 var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
83 FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
84 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
85 and c.relnamespace=n.oid and n.nspname='%s'
86 and a.attname not like '....%%' AND a.attnum > 0
87 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
89 // get primary key etc -- from Freek Dijkstra
90 var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
91 FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
92 WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid
93 AND (i.indkey[0] = a.attnum OR i.indkey[1] = a.attnum OR i.indkey[2] = a.attnum OR i.indkey[3] = a.attnum OR i.indkey[4] = a.attnum OR i.indkey[5] = a.attnum OR i.indkey[6] = a.attnum OR i.indkey[7] = a.attnum)
94 AND a.attrelid = bc.oid AND bc.relname = '%s'";
96 var $hasAffectedRows = true;
97 var $hasLimit = false; // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
98 // below suggested by Freek Dijkstra
99 var $true = 'TRUE'; // string that represents TRUE for a database
100 var $false = 'FALSE'; // string that represents FALSE for a database
101 var $fmtDate = "'Y-m-d'"; // used by DBDate() as the default date format used by the database
102 var $fmtTimeStamp = "'Y-m-d H:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
103 var $hasMoveFirst = true;
104 var $hasGenID = true;
105 var $_genIDSQL = "SELECT NEXTVAL('%s')";
106 var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
107 var $_dropSeqSQL = "DROP SEQUENCE %s";
108 var $metaDefaultsSQL = "SELECT d.adnum as num, d.adsrc as def from pg_attrdef d, pg_class c where d.adrelid=c.oid and c.relname='%s' order by d.adnum";
109 var $random = 'random()'; /// random function
110 var $autoRollback = true; // apparently pgsql does not autorollback properly before php 4.3.4
111 // http://bugs.php.net/bug.php?id=25404
113 var $uniqueIisR = true;
114 var $_bindInputArray = false; // requires postgresql 7.3+ and ability to modify database
115 var $disableBlobs = false; // set to true to disable blob checking, resulting in 2-5% improvement in performance.
119 // The last (fmtTimeStamp is not entirely correct:
120 // PostgreSQL also has support for time zones,
121 // and writes these time in this format: "2001-03-01 18:59:26+02".
122 // There is no code for the "+02" time zone information, so I just left that out.
123 // I'm not familiar enough with both ADODB as well as Postgres
124 // to know what the concequences are. The other values are correct (wheren't in 0.94)
127 function __construct()
129 // changes the metaColumnsSQL, adds columns: attnum[6]
132 function ServerInfo()
134 if (isset($this->version)) return $this->version;
136 $arr['description'] = $this->GetOne("select version()");
137 $arr['version'] = ADOConnection::_findvers($arr['description']);
138 $this->version = $arr;
142 function IfNull( $field, $ifNull )
144 return " coalesce($field, $ifNull) ";
147 // get the last id - never tested
148 function pg_insert_id($tablename,$fieldname)
150 $result=pg_query($this->_connectionID, 'SELECT last_value FROM '. $tablename .'_'. $fieldname .'_seq');
152 $arr = @pg_fetch_row($result,0);
153 pg_free_result($result);
154 if (isset($arr[0])) return $arr[0];
160 * Warning from https://www.php.net/manual/function.pg-getlastoid.php:
161 * Using a OID as a unique identifier is not generally wise.
162 * Unless you are very careful, you might end up with a tuple having
163 * a different OID if a database must be reloaded.
165 function _insertid($table,$column)
167 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
168 $oid = pg_getlastoid($this->_resultid);
169 // to really return the id, we need the table and column-name, else we can only return the oid != id
170 return empty($table) || empty($column) ? $oid : $this->GetOne("SELECT $column FROM $table WHERE oid=".(int)$oid);
173 function _affectedrows()
175 if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
176 return pg_affected_rows($this->_resultid);
183 function BeginTrans()
185 if ($this->transOff) return true;
186 $this->transCnt += 1;
187 return pg_query($this->_connectionID, 'begin '.$this->_transmode);
190 function RowLock($tables,$where,$col='1 as adodbignore')
192 if (!$this->transCnt) $this->BeginTrans();
193 return $this->GetOne("select $col from $tables where $where for update");
196 // returns true/false.
197 function CommitTrans($ok=true)
199 if ($this->transOff) return true;
200 if (!$ok) return $this->RollbackTrans();
202 $this->transCnt -= 1;
203 return pg_query($this->_connectionID, 'commit');
206 // returns true/false
207 function RollbackTrans()
209 if ($this->transOff) return true;
210 $this->transCnt -= 1;
211 return pg_query($this->_connectionID, 'rollback');
214 function MetaTables($ttype=false,$showSchema=false,$mask=false)
216 $info = $this->ServerInfo();
217 if ($info['version'] >= 7.3) {
218 $this->metaTablesSQL = "
219 select table_name,'T' from information_schema.tables where table_schema not in ( 'pg_catalog','information_schema')
221 select table_name,'V' from information_schema.views where table_schema not in ( 'pg_catalog','information_schema') ";
224 $save = $this->metaTablesSQL;
225 $mask = $this->qstr(strtolower($mask));
226 if ($info['version']>=7.3)
227 $this->metaTablesSQL = "
228 select table_name,'T' from information_schema.tables where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema')
230 select table_name,'V' from information_schema.views where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema') ";
232 $this->metaTablesSQL = "
233 select tablename,'T' from pg_tables where tablename like $mask
235 select viewname,'V' from pg_views where viewname like $mask";
237 $ret = ADOConnection::MetaTables($ttype,$showSchema);
240 $this->metaTablesSQL = $save;
246 // if magic quotes disabled, use pg_escape_string()
247 function qstr($s,$magic_quotes=false)
249 if (is_bool($s)) return $s ? 'true' : 'false';
251 if (!$magic_quotes) {
252 if (ADODB_PHPVER >= 0x5200 && $this->_connectionID) {
253 return "'".pg_escape_string($this->_connectionID,$s)."'";
255 if (ADODB_PHPVER >= 0x4200) {
256 return "'".pg_escape_string($s)."'";
258 if ($this->replaceQuote[0] == '\\'){
259 $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\\000"),$s);
261 return "'".str_replace("'",$this->replaceQuote,$s)."'";
264 // undo magic quotes for "
265 $s = str_replace('\\"','"',$s);
271 // Format date column in sql string given an input format that understands Y M D
272 function SQLDate($fmt, $col=false)
274 if (!$col) $col = $this->sysTimeStamp;
275 $s = 'TO_CHAR('.$col.",'";
278 for ($i=0; $i < $len; $i++) {
336 // handle escape characters...
339 $ch = substr($fmt,$i,1);
341 if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
342 else $s .= '"'.$ch.'"';
352 * Load a Large Object from a file
353 * - the procedure stores the object id in the table and imports the object using
354 * postgres proprietary blob handling routines
356 * contributed by Mattia Rossi mattia@technologist.com
357 * modified for safe mode by juraj chlebec
359 function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
361 pg_query($this->_connectionID, 'begin');
363 $fd = fopen($path,'r');
364 $contents = fread($fd,filesize($path));
367 $oid = pg_lo_create($this->_connectionID);
368 $handle = pg_lo_open($this->_connectionID, $oid, 'w');
369 pg_lo_write($handle, $contents);
370 pg_lo_close($handle);
372 // $oid = pg_lo_import ($path);
373 pg_query($this->_connectionID, 'commit');
374 $rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype);
380 * Deletes/Unlinks a Blob from the database, otherwise it
381 * will be left behind
383 * Returns TRUE on success or FALSE on failure.
385 * contributed by Todd Rogers todd#windfox.net
387 function BlobDelete( $blob )
389 pg_query($this->_connectionID, 'begin');
390 $result = @pg_lo_unlink($blob);
391 pg_query($this->_connectionID, 'commit');
396 Hueristic - not guaranteed to work.
398 function GuessOID($oid)
400 if (strlen($oid)>16) return false;
401 return is_numeric($oid);
405 * If an OID is detected, then we use pg_lo_* to open the oid file and read the
406 * real blob from the db using the oid supplied as a parameter. If you are storing
407 * blobs using bytea, we autodetect and process it so this function is not needed.
409 * contributed by Mattia Rossi mattia@technologist.com
411 * see http://www.postgresql.org/idocs/index.php?largeobjects.html
413 * Since adodb 4.54, this returns the blob, instead of sending it to stdout. Also
414 * added maxsize parameter, which defaults to $db->maxblobsize if not defined.
416 function BlobDecode($blob,$maxsize=false,$hastrans=true)
418 if (!$this->GuessOID($blob)) return $blob;
420 if ($hastrans) pg_query($this->_connectionID,'begin');
421 $fd = @pg_lo_open($this->_connectionID,$blob,'r');
423 if ($hastrans) pg_query($this->_connectionID,'commit');
426 if (!$maxsize) $maxsize = $this->maxblobsize;
427 $realblob = @pg_lo_read($fd,$maxsize);
429 if ($hastrans) pg_query($this->_connectionID,'commit');
434 See http://www.postgresql.org/idocs/index.php?datatype-binary.html
436 NOTE: SQL string literals (input strings) must be preceded with two backslashes
437 due to the fact that they must pass through two parsers in the PostgreSQL
440 function BlobEncode($blob)
442 if (ADODB_PHPVER >= 0x5200) return pg_escape_bytea($this->_connectionID, $blob);
443 if (ADODB_PHPVER >= 0x4200) return pg_escape_bytea($blob);
445 /*92=backslash, 0=null, 39=single-quote*/
446 $badch = array(chr(92),chr(0),chr(39)); # \ null '
447 $fixch = array('\\\\134','\\\\000','\\\\047');
448 return adodb_str_replace($badch,$fixch,$blob);
450 // note that there is a pg_escape_bytea function only for php 4.2.0 or later
453 // assumes bytea for blob, and varchar for clob
454 function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
456 if ($blobtype == 'CLOB') {
457 return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where");
459 // do not use bind params which uses qstr(), as blobencode() already quotes data
460 return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where");
463 function OffsetDate($dayFraction,$date=false)
465 if (!$date) $date = $this->sysDate;
466 else if (strncmp($date,"'",1) == 0) {
467 $len = strlen($date);
468 if (10 <= $len && $len <= 12) $date = 'date '.$date;
469 else $date = 'timestamp '.$date;
473 return "($date+interval'".($dayFraction * 1440)." minutes')";
474 #return "($date+interval'$dayFraction days')";
478 * Generate the SQL to retrieve MetaColumns data
479 * @param string $table Table name
480 * @param string $schema Schema name (can be blank)
481 * @return string SQL statement to execute
483 protected function _generateMetaColumnsSQL($table, $schema)
486 return sprintf($this->metaColumnsSQL1, $table, $table, $schema);
489 return sprintf($this->metaColumnsSQL, $table, $table, $schema);
493 // for schema support, pass in the $table param "$schema.$tabname".
494 // converts field names to lowercase, $upper is ignored
495 // see PHPLens Issue No: 14018 for more info
496 function MetaColumns($table,$normalize=true)
498 global $ADODB_FETCH_MODE;
502 $this->_findschema($table,$schema);
504 if ($normalize) $table = strtolower($table);
506 $save = $ADODB_FETCH_MODE;
507 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
508 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
510 $rs = $this->Execute($this->_generateMetaColumnsSQL($table, $schema));
511 if (isset($savem)) $this->SetFetchMode($savem);
512 $ADODB_FETCH_MODE = $save;
517 if (!empty($this->metaKeySQL)) {
518 // If we want the primary keys, we have to issue a separate query
519 // Of course, a modified version of the metaColumnsSQL query using a
520 // LEFT JOIN would have been much more elegant, but postgres does
521 // not support OUTER JOINS. So here is the clumsy way.
523 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
525 $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
526 // fetch all result in once for performance.
527 $keys = $rskey->GetArray();
528 if (isset($savem)) $this->SetFetchMode($savem);
529 $ADODB_FETCH_MODE = $save;
536 if (!empty($this->metaDefaultsSQL)) {
537 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
538 $sql = sprintf($this->metaDefaultsSQL, ($table));
539 $rsdef = $this->Execute($sql);
540 if (isset($savem)) $this->SetFetchMode($savem);
541 $ADODB_FETCH_MODE = $save;
544 while (!$rsdef->EOF) {
545 $num = $rsdef->fields['num'];
546 $s = $rsdef->fields['def'];
547 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
549 $s = substr($s, 0, strlen($s) - 1);
556 ADOConnection::outp( "==> SQL => " . $sql);
563 $fld = new ADOFieldObject();
564 $fld->name = $rs->fields[0];
565 $fld->type = $rs->fields[1];
566 $fld->max_length = $rs->fields[2];
567 $fld->attnum = $rs->fields[6];
569 if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
570 if ($fld->max_length <= 0) $fld->max_length = -1;
571 if ($fld->type == 'numeric') {
572 $fld->scale = $fld->max_length & 0xFFFF;
573 $fld->max_length >>= 16;
576 // 5 hasdefault; 6 num-of-column
577 $fld->has_default = ($rs->fields[5] == 't');
578 if ($fld->has_default) {
579 $fld->default_value = $rsdefa[$rs->fields[6]];
583 $fld->not_null = $rs->fields[4] == 't';
587 if (is_array($keys)) {
588 foreach($keys as $key) {
589 if ($fld->name == $key['column_name'] AND $key['primary_key'] == 't')
590 $fld->primary_key = true;
591 if ($fld->name == $key['column_name'] AND $key['unique_key'] == 't')
592 $fld->unique = true; // What name is more compatible?
596 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
597 else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
609 function Param($name,$type='C')
614 // Reset param num if $name is false
617 return '$'.$this->_pnum;
620 function MetaIndexes ($table, $primary = FALSE, $owner = false)
622 global $ADODB_FETCH_MODE;
625 $this->_findschema($table,$schema);
627 if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
629 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
630 FROM pg_catalog.pg_class c
631 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
632 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
634 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))
635 and c.relnamespace=c2.relnamespace
636 and c.relnamespace=n.oid
637 and n.nspname=\'%s\'';
640 SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
641 FROM pg_catalog.pg_class c
642 JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
643 JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
644 WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))';
647 if ($primary == FALSE) {
648 $sql .= ' AND i.indisprimary=false;';
651 $save = $ADODB_FETCH_MODE;
652 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
653 if ($this->fetchMode !== FALSE) {
654 $savem = $this->SetFetchMode(FALSE);
657 $rs = $this->Execute(sprintf($sql,$table,$table,$schema));
659 $this->SetFetchMode($savem);
661 $ADODB_FETCH_MODE = $save;
663 if (!is_object($rs)) {
668 $col_names = $this->MetaColumnNames($table,true,true);
669 // 3rd param is use attnum,
670 // see https://sourceforge.net/p/adodb/bugs/45/
672 while ($row = $rs->FetchRow()) {
674 foreach (explode(' ', $row[2]) as $col) {
675 $columns[] = $col_names[$col];
678 $indexes[$row[0]] = array(
679 'unique' => ($row[1] == 't'),
680 'columns' => $columns
686 // returns true or false
689 // $db->Connect("host=host1 user=user1 password=secret port=4341");
690 // $db->Connect('host1','user1','secret');
691 function _connect($str,$user='',$pwd='',$db='',$ctype=0)
693 if (!function_exists('pg_connect')) return null;
695 $this->_errorMsg = false;
697 if ($user || $pwd || $db) {
698 $user = adodb_addslashes($user);
699 $pwd = adodb_addslashes($pwd);
700 if (strlen($db) == 0) $db = 'template1';
701 $db = adodb_addslashes($db);
703 $host = explode(":", $str);
704 if ($host[0]) $str = "host=".adodb_addslashes($host[0]);
706 if (isset($host[1])) $str .= " port=$host[1]";
707 else if (!empty($this->port)) $str .= " port=".$this->port;
709 if ($user) $str .= " user=".$user;
710 if ($pwd) $str .= " password=".$pwd;
711 if ($db) $str .= " dbname=".$db;
714 //if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
716 if ($ctype === 1) { // persistent
717 $this->_connectionID = pg_pconnect($str);
719 if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
722 if (empty($ncnt)) $ncnt = 1;
725 $str .= str_repeat(' ',$ncnt);
727 $this->_connectionID = pg_connect($str);
729 if ($this->_connectionID === false) return false;
730 $this->Execute("set datestyle='ISO'");
732 $info = $this->ServerInfo();
733 $this->pgVersion = (float) substr($info['version'],0,3);
734 if ($this->pgVersion >= 7.1) { // good till version 999
735 $this->_nestedSQL = true;
738 # PostgreSQL 9.0 changed the default output for bytea from 'escape' to 'hex'
739 # PHP does not handle 'hex' properly ('x74657374' is returned as 't657374')
740 # https://bugs.php.net/bug.php?id=59831 states this is in fact not a bug,
741 # so we manually set bytea_output
742 if ( !empty($this->connection->noBlobs) && version_compare($info['version'], '9.0', '>=')) {
743 $this->Execute('set bytea_output=escape');
749 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
751 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
754 // returns true or false
757 // $db->PConnect("host=host1 user=user1 password=secret port=4341");
758 // $db->PConnect('host1','user1','secret');
759 function _pconnect($str,$user='',$pwd='',$db='')
761 return $this->_connect($str,$user,$pwd,$db,1);
765 // returns queryID or false
766 function _query($sql,$inputarr=false)
769 $this->_errorMsg = false;
772 It appears that PREPARE/EXECUTE is slower for many queries.
774 For query executed 1000 times:
775 "select id,firstname,lastname from adoxyz
776 where firstname not like ? and lastname not like ? and id = ?"
778 with plan = 1.51861286163 secs
779 no plan = 1.26903700829 secs
781 $plan = 'P'.md5($sql);
784 foreach($inputarr as $v) {
785 if ($execp) $execp .= ',';
787 if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
793 if ($execp) $exsql = "EXECUTE $plan ($execp)";
794 else $exsql = "EXECUTE $plan";
797 $rez = @pg_execute($this->_connectionID,$exsql);
799 # Perhaps plan does not exist? Prepare/compile plan.
801 foreach($inputarr as $v) {
802 if ($params) $params .= ',';
804 $params .= 'VARCHAR';
805 } else if (is_integer($v)) {
806 $params .= 'INTEGER';
811 $sqlarr = explode('?',$sql);
815 foreach($sqlarr as $v) {
819 $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
821 $rez = pg_execute($this->_connectionID,$s);
822 //echo $this->ErrorMsg();
825 $rez = pg_execute($this->_connectionID,$exsql);
828 $rez = pg_query($this->_connectionID,$sql);
830 // check if no data returned, then no need to create real recordset
831 if ($rez && pg_num_fields($rez) <= 0) {
832 if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
833 pg_free_result($this->_resultid);
835 $this->_resultid = $rez;
842 function _errconnect()
844 if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
845 else return 'Database connection failed';
848 /* Returns: the last error message from previous database operation */
851 if ($this->_errorMsg !== false) return $this->_errorMsg;
852 if (ADODB_PHPVER >= 0x4300) {
853 if (!empty($this->_resultid)) {
854 $this->_errorMsg = @pg_result_error($this->_resultid);
855 if ($this->_errorMsg) return $this->_errorMsg;
858 if (!empty($this->_connectionID)) {
859 $this->_errorMsg = @pg_last_error($this->_connectionID);
860 } else $this->_errorMsg = $this->_errconnect();
862 if (empty($this->_connectionID)) $this->_errconnect();
863 else $this->_errorMsg = @pg_errormessage($this->_connectionID);
865 return $this->_errorMsg;
870 $e = $this->ErrorMsg();
872 return ADOConnection::MetaError($e);
877 // returns true or false
880 if ($this->transCnt) $this->RollbackTrans();
881 if ($this->_resultid) {
882 @pg_free_result($this->_resultid);
883 $this->_resultid = false;
885 @pg_close($this->_connectionID);
886 $this->_connectionID = false;
892 * Maximum size of C field
896 return 1000000000; // should be 1 Gb?
900 * Maximum size of X field
904 return 1000000000; // should be 1 Gb?
910 /*--------------------------------------------------------------------------------------
911 Class Name: Recordset
912 --------------------------------------------------------------------------------------*/
914 class ADORecordSet_postgres64 extends ADORecordSet{
916 var $databaseType = "postgres64";
919 function __construct($queryID, $mode=false)
921 if ($mode === false) {
922 global $ADODB_FETCH_MODE;
923 $mode = $ADODB_FETCH_MODE;
927 case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
928 case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
930 case ADODB_FETCH_DEFAULT:
931 case ADODB_FETCH_BOTH:
932 default: $this->fetchMode = PGSQL_BOTH; break;
934 $this->adodbFetchMode = $mode;
936 // Parent's constructor
937 parent::__construct($queryID);
940 function GetRowAssoc($upper = ADODB_ASSOC_CASE)
942 if ($this->fetchMode == PGSQL_ASSOC && $upper == ADODB_ASSOC_CASE_LOWER) {
943 return $this->fields;
945 $row = ADORecordSet::GetRowAssoc($upper);
952 global $ADODB_COUNTRECS;
953 $qid = $this->_queryID;
954 $this->_numOfRows = ($ADODB_COUNTRECS)? @pg_num_rows($qid):-1;
955 $this->_numOfFields = @pg_num_fields($qid);
957 // cache types for blob decode check
958 // apparently pg_field_type actually performs an sql query on the database to get the type.
959 if (empty($this->connection->noBlobs))
960 for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
961 if (pg_field_type($qid,$i) == 'bytea') {
962 $this->_blobArr[$i] = pg_field_name($qid,$i);
967 /* Use associative array to get fields array */
968 function Fields($colname)
970 if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
973 $this->bind = array();
974 for ($i=0; $i < $this->_numOfFields; $i++) {
975 $o = $this->FetchField($i);
976 $this->bind[strtoupper($o->name)] = $i;
979 return $this->fields[$this->bind[strtoupper($colname)]];
982 function FetchField($off = 0)
984 // offsets begin at 0
986 $o= new ADOFieldObject();
987 $o->name = @pg_field_name($this->_queryID,$off);
988 $o->type = @pg_field_type($this->_queryID,$off);
989 $o->max_length = @pg_fieldsize($this->_queryID,$off);
995 return @pg_fetch_row($this->_queryID,$row);
998 function _decode($blob)
1000 if ($blob === NULL) return NULL;
1001 // eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
1002 return pg_unescape_bytea($blob);
1005 function _fixblobs()
1007 if ($this->fetchMode == PGSQL_NUM || $this->fetchMode == PGSQL_BOTH) {
1008 foreach($this->_blobArr as $k => $v) {
1009 $this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
1012 if ($this->fetchMode == PGSQL_ASSOC || $this->fetchMode == PGSQL_BOTH) {
1013 foreach($this->_blobArr as $k => $v) {
1014 $this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
1019 // 10% speedup to move MoveNext to child class
1023 $this->_currentRow++;
1024 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
1025 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1026 if (is_array($this->fields) && $this->fields) {
1027 if (isset($this->_blobArr)) $this->_fixblobs();
1031 $this->fields = false;
1040 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
1043 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1045 if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
1047 return (is_array($this->fields));
1052 return @pg_free_result($this->_queryID);
1055 function MetaType($t,$len=-1,$fieldobj=false)
1057 if (is_object($t)) {
1059 $t = $fieldobj->type;
1060 $len = $fieldobj->max_length;
1062 switch (strtoupper($t)) {
1063 case 'MONEY': // stupid, postgres expects money to be a string
1073 if ($len <= $this->blobSize) return 'C';
1078 case 'IMAGE': // user defined type
1079 case 'BLOB': // user defined type
1080 case 'BIT': // This is a bit string, not a single bit, so don't return 'L'
1093 case 'TIMESTAMP WITHOUT TIME ZONE':
1106 if (isset($fieldobj) &&
1107 empty($fieldobj->primary_key) && (!$this->connection->uniqueIisR || empty($fieldobj->unique))) return 'I';