pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgresql primarykeys foreignkeys tablenames screenfieldlength
3+ messages / 2 participants
[nested] [flat]

* postgresql primarykeys foreignkeys tablenames screenfieldlength
@ 2003-01-21 06:58 Kevin Gordon <kgordon@paradise.net.nz>
  2003-01-21 07:25 ` Re: postgresql primarykeys foreignkeys tablenames screenfieldlength Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 3+ messages in thread

From: Kevin Gordon @ 2003-01-21 06:58 UTC (permalink / raw)
  To: pgsql-php

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;
			}
		}









^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: postgresql primarykeys foreignkeys tablenames screenfieldlength
  2003-01-21 06:58 postgresql primarykeys foreignkeys tablenames screenfieldlength Kevin Gordon <kgordon@paradise.net.nz>
@ 2003-01-21 07:25 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2003-01-23 08:05   ` Re: postgresql primarykeys foreignkeys tablenames Kevin Gordon <kgordon@paradise.net.nz>
  0 siblings, 1 reply; 3+ messages in thread

From: Christopher Kings-Lynne @ 2003-01-21 07:25 UTC (permalink / raw)
  To: Kevin Gordon <kgordon@paradise.net.nz>; pgsql-php

I suggest looking at the source code for postgres's JDBC driver or WebDB
(http://phpgpadmin.sourceforge.net/).

Chris

> -----Original Message-----
> From: pgsql-php-owner@postgresql.org
> [mailto:pgsql-php-owner@postgresql.org]On Behalf Of Kevin Gordon
> Sent: Tuesday, 21 January 2003 2:58 PM
> To: pgsql-php@postgresql.org
> Subject: [PHP] postgresql primarykeys foreignkeys tablenames
> screenfieldlength
>
>
> 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;
> 			}
> 		}
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: postgresql primarykeys foreignkeys tablenames
  2003-01-21 06:58 postgresql primarykeys foreignkeys tablenames screenfieldlength Kevin Gordon <kgordon@paradise.net.nz>
  2003-01-21 07:25 ` Re: postgresql primarykeys foreignkeys tablenames screenfieldlength Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-01-23 08:05   ` Kevin Gordon <kgordon@paradise.net.nz>
  0 siblings, 0 replies; 3+ messages in thread

From: Kevin Gordon @ 2003-01-23 08:05 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-php

Many thanks Chris for your suggestion re phppgadmin. The code very
interesting. I could not find any code re foreign keys but a good source
of examples and techique. My code works ok. Just thought it may be
improved. Thanks again.
Kevin

On Tue, 2003-01-21 at 20:25, Christopher Kings-Lynne wrote:
> I suggest looking at the source code for postgres's JDBC driver or WebDB
> (http://phpgpadmin.sourceforge.net/).
> 
> Chris
> 
> > -----Original Message-----
> > From: pgsql-php-owner@postgresql.org
> > [mailto:pgsql-php-owner@postgresql.org]On Behalf Of Kevin Gordon
> > Sent: Tuesday, 21 January 2003 2:58 PM
> > To: pgsql-php@postgresql.org
> > Subject: [PHP] postgresql primarykeys foreignkeys tablenames
> > screenfieldlength
> >
> >
> > 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;
> > 			}
> > 		}
> >
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> 
> 





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-01-23 08:05 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-21 06:58 postgresql primarykeys foreignkeys tablenames screenfieldlength Kevin Gordon <kgordon@paradise.net.nz>
2003-01-21 07:25 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-01-23 08:05   ` Re: postgresql primarykeys foreignkeys tablenames Kevin Gordon <kgordon@paradise.net.nz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox