114
/* The following does not work in Pg 6.0 - does anyone want to contribute code?
116
//"ALTER TABLE table ALTER COLUMN column SET DEFAULT mydef" and
117
//"ALTER TABLE table ALTER COLUMN column DROP DEFAULT mydef"
118
//"ALTER TABLE table ALTER COLUMN column SET NOT NULL" and
119
//"ALTER TABLE table ALTER COLUMN column DROP NOT NULL"*/
120
function AlterColumnSQL($tabname, $flds)
122
if ($this->debug) ADOConnection::outp("AlterColumnSQL not supported for PostgreSQL");
119
* Adding a new Column
121
* reimplementation of the default function as postgres does NOT allow to set the default in the same statement
123
* @param string $tabname table-name
124
* @param string $flds column-names and types for the changed columns
125
* @return array with SQL strings
127
function AddColumnSQL($tabname, $flds)
129
$tabname = $this->TableName ($tabname);
131
list($lines,$pkey) = $this->_GenFields($flds);
132
$alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
133
foreach($lines as $v) {
134
if (($not_null = preg_match('/NOT NULL/i',$v))) {
135
$v = preg_replace('/NOT NULL/i','',$v);
137
if (preg_match('/^([^ ]+) .*(DEFAULT [^ ]+)/',$v,$matches)) {
138
list(,$colname,$default) = $matches;
139
$sql[] = $alter . str_replace($default,'',$v);
140
$sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET ' . $default;
142
$sql[] = $alter . $v;
145
list($colname) = explode(' ',$v);
146
$sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
153
* Change the definition of one column
155
* Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
156
* to allow, recreating the table and copying the content over to the new table
157
* @param string $tabname table-name
158
* @param string $flds column-name and type for the changed column
159
* @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
160
* @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
161
* @return array with SQL strings
163
function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
166
if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
169
return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
175
* Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
176
* to allow, recreating the table and copying the content over to the new table
177
* @param string $tabname table-name
178
* @param string $flds column-name and type for the changed column
179
* @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
180
* @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
181
* @return array with SQL strings
183
function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
185
$has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
186
if (!$has_drop_column && !$tableflds) {
187
if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
127
function DropColumnSQL($tabname, $flds)
129
if ($this->debug) ADOConnection::outp("DropColumnSQL only works with PostgreSQL 7.3+");
130
return ADODB_DataDict::DropColumnSQL($tabname, $flds)."/* only works for PostgreSQL 7.3+ */";
190
if ($has_drop_column) {
191
return ADODB_DataDict::DropColumnSQL($tabname, $flds);
193
return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
197
* Save the content into a temp. table, drop and recreate the original table and copy the content back in
199
* We also take care to set the values of the sequenz and recreate the indexes.
200
* All this is done in a transaction, to not loose the content of the table, if something went wrong!
202
* @param string $tabname table-name
203
* @param string $dropflds column-names to drop
204
* @param string $tableflds complete defintion of the new table, eg. for postgres
205
* @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
206
* @return array with SQL strings
208
function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
210
if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
212
foreach($this->MetaColumns($tabname) as $fld) {
213
if (!$dropflds || !in_array($fld->name,$dropflds)) {
214
// we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
215
if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
216
in_array($fld->type,array('varchar','char','text','bytea'))) {
217
$copyflds[] = "to_number($fld->name,'S99D99')";
219
$copyflds[] = $fld->name;
221
// identify the sequence name and the fld its on
222
if ($fld->primary_key && $fld->has_default &&
223
preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
224
$seq_name = $matches[1];
225
$seq_fld = $fld->name;
229
$copyflds = implode(', ',$copyflds);
231
$tempname = $tabname.'_tmp';
232
$aSql[] = 'BEGIN'; // we use a transaction, to make sure not to loose the content of the table
233
$aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
234
$aSql = array_merge($aSql,$this->DropTableSQL($tabname));
235
$aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
236
$aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
237
if ($seq_name && $seq_fld) { // if we have a sequence we need to set it again
238
$seq_name = $tabname.'_'.$seq_fld.'_seq'; // has to be the name of the new implicit sequence
239
$aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
241
$aSql[] = "DROP TABLE $tempname";
242
// recreate the indexes, if they not contain one of the droped columns
243
foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
245
if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
246
$aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
247
$idx_data['unique'] ? array('UNIQUE') : False));
254
function DropTableSQL($tabname)
256
$sql = ADODB_DataDict::DropTableSQL($tabname);
258
$drop_seq = $this->_DropAutoIncrement($tabname);
259
if ($drop_seq) $sql[] = $drop_seq;
133
264
// return string must begin with space