pgsql: specialized casts for _recreate_copy_table()

Current PostgreSQL versions wont allow an ALTER COLUMN between varchar
and bytea (blob) without an explicit cast. This pull request adds this
casts for both directions and fixes sequence name detection to
understand newer ::regclass instead of ::text in defaults.

Fixes #207

Signed-off-by: Damien Regad <dregad@mantisbt.org>

Original commits in PR #207 squashed and message reworded.

1 files changed, 27 insertions, 13 deletions

diff --git a/datadict/datadict-postgres.inc.php b/datadict/datadict-postgres.inc.php
index 243c7b89..8fcb5712 100644
--- a/datadict/datadict-postgres.inc.php
+++ b/datadict/datadict-postgres.inc.php
@@ -312,20 +312,34 @@ class ADODB2_postgres extends ADODB_DataDict {
 		if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
 		$copyflds = array();
 		foreach($this->MetaColumns($tabname) as $fld) {
-			if (!$dropflds || !in_array($fld->name,$dropflds)) {
-				// we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
-				if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
-					in_array($fld->type,array('varchar','char','text','bytea'))) {
+			if (preg_match('/'.$fld->name.' (\w+)/i', $tableflds, $matches)) {
+				$new_type = strtoupper($matches[1]);
+				// AlterColumn of a char column to a nummeric one needs an explicit conversation
+				if (in_array($new_type, array('I', 'I2', 'I4', 'I8', 'N', 'F')) &&
+					in_array($fld->type, array('varchar','char','text','bytea'))
+				) {
 					$copyflds[] = "to_number($fld->name,'S9999999999999D99')";
 				} else {
-					$copyflds[] = $fld->name;
-				}
-				// identify the sequence name and the fld its on
-				if ($fld->primary_key && $fld->has_default &&
-					preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
-					$seq_name = $matches[1];
-					$seq_fld = $fld->name;
+					// other column-type changes needs explicit decode, encode for bytea or cast otherwise
+					$new_actual_type = $this->ActualType($new_type);
+					if (strtoupper($fld->type) != $new_actual_type) {
+						if ($new_actual_type == 'BYTEA' && $fld->type == 'text') {
+							$copyflds[] = "DECODE($fld->name, 'escape')";
+						} elseif ($fld->type == 'bytea' && $new_actual_type == 'TEXT') {
+							$copyflds[] = "ENCODE($fld->name, 'escape')";
+						} else {
+							$copyflds[] = "CAST($fld->name AS $new_actual_type)";
+						}
+					}
 				}
+			} else {
+				$copyflds[] = $fld->name;
+			}
+			// identify the sequence name and the fld its on
+			if ($fld->primary_key && $fld->has_default &&
+				preg_match("/nextval\('([^']+)'::(text|regclass)\)/",$fld->default_value,$matches)) {
+				$seq_name = $matches[1];
+				$seq_fld = $fld->name;
 			}
 		}
 		$copyflds = implode(', ',$copyflds);
@@ -479,7 +493,7 @@ CREATE [ UNIQUE ] INDEX index_name ON table
 			if (strlen($fprec)) $ftype .= ",".$fprec;
 			$ftype .= ')';
 		}
-		
+
 		/*
 		* Handle additional options
 		*/
@@ -492,7 +506,7 @@ CREATE [ UNIQUE ] INDEX index_name ON table
 					case 'ENUM':
 					$ftype .= '(' . $value . ')';
 					break;
-					
+
 					default:
 				}
 			}