Php Programming Code Examples
Php > Database Related Code Examples
Better PostgreSQL Metadata Function
Better PostgreSQL Metadata Function
<?php
// Returns array contains metadata
// $db: Database connection resource
// $table: Table name
function MetaData($db, $table) {
$rows = 0; // Number of rows
$qid = 0; // Query result resource
$meta = array(); // Metadata array - return value
$sql = 'SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class as c, pg_attribute a, pg_type t WHERE a.attnum > 0 and a.attrelid = c.oid and c.relname = '."'$table'".' and a.atttypid = t.oid order by a.attnum';
$qid = pg_Exec($db, $sql);
// Check error
if (!is_resource($qid)) {
print('MetaData(): Query Error - table does not exist');
return null;
}
$rows = pg_NumRows($qid);
// Store meta data
for ($i = 0; $i < $rows; $i++) {
$field_name = pg_Result($qid,$i,1); // Field Name
$meta[$field_name]['id'] = pg_Result($qid,$i,0); // Attrbute ID
$meta[$field_name]['type'] = pg_Result($qid,$i,2); // Data type name
$meta[$field_name]['len'] = pg_Result($qid,$i,3); // Length: -1 for variable length
$meta[$field_name]['modifier'] = pg_Result($qid,$i,4); // Modifier
$meta[$field_name]['notnull'] = (pg_Result($qid,$i,5) === 't' ? TRUE : FALSE); // Not NULL?
$meta[$field_name]['hasdefault'] = (pg_Result($qid,$i,6) === 't' ? TRUE : FALSE); // Has default value?
}
// Clean up. PHP4 reference count code would be smart enough to do this, though.
pg_FreeResult($qid);
return $meta;
}
//// Test code ////
$dbName = 'db_session'; // Change this to your db name
$dbUser = 'yohgaki'; // Change this to your db user name
$tableName = 'sys_session'; // Change this to your table name
$db = pg_connect('host=dev dbname='.$dbName.' user='.$dbUser);
$meta = metadata($db, $tableName);
print("<pre>\n");
print_r($meta);
print("</pre>\n");
?>
