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
';
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],
";
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 . ' />';
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]
";
$phrase = split("\(|\)", $r[1]);
echo "Phrase: $phrase[0], $phrase[1], $phrase[2], $phrase[3],
$phrase[4]
";
// $obj->{$i} = $len[1];
$kgArr[$i][0][0] = $tablename;
$word1 = split(",", $phrase[1]);
echo (count($word1));
echo "Word: $word1[0]; $word1[1]
";
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]
";
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 .
'
';
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]
";
$len = split("\(|\)", $r[1]);
echo "Length: $len[0], $len[1], $len[2], $len[3]
";
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 .
'
';
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)

Re: postgresql primarykeys foreignkeys tablenames screenfieldlength

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
';
> 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],
";
> 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 . ' > />';
> 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]
";
> $phrase = split("\(|\)", $r[1]);
> echo "Phrase: $phrase[0],
> $phrase[1], $phrase[2], $phrase[3],
> $phrase[4]
";
> // $obj->{$i} = $len[1];
> $kgArr[$i][0][0] = $tablename;
> $word1 = split(",", $phrase[1]);
> echo (count($word1));
> echo "Word: $word1[0]; $word1[1]
";
> 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]
";
> 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 .
> '
';
> 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]
";
> $len = split("\(|\)", $r[1]);
> echo "Length: $len[0], $len[1],
> $len[2], $len[3]
";
> 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 .
> '
';
> 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)
>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: postgresql primarykeys foreignkeys tablenames

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
';
> > 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],
";
> > 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 . ' > > />';
> > 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]
";
> > $phrase = split("\(|\)", $r[1]);
> > echo "Phrase: $phrase[0],
> > $phrase[1], $phrase[2], $phrase[3],
> > $phrase[4]
";
> > // $obj->{$i} = $len[1];
> > $kgArr[$i][0][0] = $tablename;
> > $word1 = split(",", $phrase[1]);
> > echo (count($word1));
> > echo "Word: $word1[0]; $word1[1]
";
> > 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]
";
> > 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 .
> > '
';
> > 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]
";
> > $len = split("\(|\)", $r[1]);
> > echo "Length: $len[0], $len[1],
> > $len[2], $len[3]
";
> > 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 .
> > '
';
> > 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)
> >
>
>



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html