postgresql primarykeys foreignkeys tablenames screenfieldlength

[Kevin Gordon <kgordon@xxxxxxxxxxxxxxx>]

Any suggestions or improvements to the following code would be much
appreciated. Please send me an email:

	function kgtables () 
		{
		if ($this->connection) 
			{
			$tablelist = "";
			$sql = "SELECT " . 
						"ic.relname " .
					"FROM " .
						"pg_class ic " .
					"WHERE " .
						"ic.relname not like 'pg%' " .
						"AND ic.relname not like '%pk' " .
						"AND ic.relname not like '%idx' ";
			$tablelist = pg_query ($this->connection, $sql);
			$this->num_tables = pg_num_rows($tablelist);
			for ($i=0; $i < $this->num_tables; $i++) 
				{
				$r = pg_fetch_row($tablelist);
				$obj->{$i + 1} = $r[0];
				}
			pg_free_result ($tablelist);
			return $obj;
			}
		else 
			{
			echo 'Error: failed to name the tables <br />';
			return 0;
			}
		}

	function kgprimarykeys ($tablename = "") 
		{
		if ( $this->connection && $tablename != "" ) 
			{
			$keylist = "";
			$sql = "SELECT " . 
						"ic.relname AS index_name, " .
						"bc.relname AS tab_name, " . 
						"ta.attname AS column_name, " .
						"i.indisunique AS unique_key, " .
						"i.indisprimary AS primary_key " .
					"FROM " .
						"pg_class bc, " .
						"pg_class ic, " .
						"pg_index i, " .
						"pg_attribute ta, " .
						"pg_attribute ia " .
					"WHERE " .
						"bc.oid = i.indrelid " .
						"AND ic.oid = i.indexrelid " .
						"AND ia.attrelid = i.indexrelid " .
						"AND ta.attrelid = bc.oid " .
						"AND bc.relname = '" . $tablename . "' " .
						"AND ta.attrelid = i.indrelid " .
						"AND ta.attnum = i.indkey[ia.attnum-1] " .
					"ORDER BY " .
						"index_name, tab_name, column_name";
			$keylist = pg_query ($this->connection, $sql);
			$this->num_primarykeys = pg_num_rows($keylist);
			$j = 1;
			for ($i=0; $i < $this->num_primarykeys; $i++) 
				{
				$r = pg_fetch_row($keylist);
//				echo "Primary Key: $r[0], $r[1], $r[2], $r[3], $r[4], </br>";
				if ( $r[4] == TRUE)
					{
					$obj->{$j} = $r[2];
					$j++;
					}
				}
			pg_free_result ($keylist);
			return $obj;
			}
		else 
			{
			echo 'Error: failed to name the primary keys in ' . $tablename . '<br
/>';
			return 0;
			}
		}

	function kgforeignkeys ($tablename = "" ) 
		{
		if ( $this->connection && $tablename != "" ) 
			{
			$keylist = "";
			$sql = "SELECT conname,
  pg_catalog.pg_get_constraintdef(oid) as condef 
						FROM pg_catalog.pg_constraint r
						WHERE r.conrelid = (SELECT c.oid
							FROM pg_catalog.pg_class c
						    LEFT JOIN pg_catalog.pg_namespace n 
							ON n.oid = c.relnamespace
							WHERE pg_catalog.pg_table_is_visible(c.oid)
      							AND c.relname ~ '^" . $tablename . "$' )
						AND r.contype = 'f'";

			$keylist = pg_query ($this->connection, $sql);
			$num_rows = pg_num_rows($keylist);
			for ($i=0; $i < $num_rows; $i++) 
				{
				$r = pg_fetch_row($keylist);
//				echo "Field: $r[0], $r[1] </br>";
				$phrase = split("\(|\)", $r[1]);
				echo "Phrase: $phrase[0], $phrase[1], $phrase[2], $phrase[3],
$phrase[4] </br>";
//				$obj->{$i} = $len[1];
				$kgArr[$i][0][0] = $tablename;
				$word1 = split(",", $phrase[1]);
				echo (count($word1));
				echo "Word: $word1[0]; $word1[1] </br>";
				for ($j=1; $j <= count($word1); $j++)
					{
					$kgArr[$i][0][$j] = trim($word1[$j - 1]);
					}
				$kgArr[$i][1][0] = trim(Substr($phrase[2], strrpos($phrase[2], "
")));
				$word2 = split(",", $phrase[3]);
				echo (count($word2));
				echo "Word: $word2[0]; $word2[1] </br>";
				for ($j=1; $j <= count($word2); $j++)
					{
					$kgArr[$i][1][$j] = trim($word2[$j - 1]);
					}
				}
			pg_free_result ($keylist);
			return $kgArr;
			}
		else 
			{
			echo 'Error: failed to obtain the foreign keys in ' . $tablename .
'<br />';
			return 0;
			}
		}

	function kgfield_length ($field_name = "", $tablename = "" ) 
		{
		if ( $this->connection && $tablename != "" ) 
			{
			$keylist = "";
			$sql = "SELECT a.attname, pg_catalog.format_type(a.atttypid,
a.atttypmod) 
						FROM pg_catalog.pg_attribute a
						WHERE a.attrelid = (SELECT c.oid
							FROM pg_catalog.pg_class c
						    LEFT JOIN pg_catalog.pg_namespace n 
							ON n.oid = c.relnamespace
							WHERE pg_catalog.pg_table_is_visible(c.oid)
      							AND c.relname ~ '^" . $tablename . "$' )
						AND a.attnum > 0 
						AND NOT a.attisdropped
						ORDER BY a.attnum";
			$keylist = pg_query ($this->connection, $sql);
			$this->num_fields = pg_num_rows($keylist);
			for ($i=0; $i < $this->num_fields; $i++) 
				{
				$tempLen = 0;
				$r = pg_fetch_row($keylist);
//				echo "Field: $r[0], $r[1] </br>";
				$len = split("\(|\)", $r[1]);
				echo "Length: $len[0], $len[1], $len[2], $len[3] </br>";
				if (trim($len[0]) == "character" || trim($len[0]) == "character
varying")
					{
					$tempLen = $len[1];
					}
				else
					{
					switch (trim($len[0]))
						{
						case "text":
							$tempLen = 64;
							break;
						case "boolean":
							$tempLen = 1;
							break;
						case "smallint":
							$tempLen = 6;
							break;
						case "integer":
							$tempLen = 10;
							break;
						case "bigint":
							$tempLen = 18;
							break;
						case "timestamp without time zone":
							$tempLen = 20;
							break;
						case "timestamp with time zone":
							$tempLen = 32;
							break;
						case "interval":
							$tempLen = 10;
							break;
						case "date":
							$tempLen = 10;
							break;
						case "numeric":
							$tempLen = trim(Substr($len[1], 0, strpos($len[1], ",")));
							echo $tempLen;
							break;
						case "decimal":
							$tempLen = trim(Substr($len[1], 0, strpos($len[1], ",")));
							break;
						case "real":
							$tempLen = 10;
							break;
						case "double precision":
							$tempLen = 20;
							break;
						}
					}
				$obj->{$r[0]} = $tempLen;
				}
			pg_free_result ($keylist);
			return $obj;
			}
		else 
			{
			echo 'Error: failed to obtain the field length in ' . $tablename .
'<br />';
			return 0;
			}
		}