This is an example code for getting PostgreSQL metadata from its system tables. I've seen several metadata functions that are not using PostgreSQL system tables, so I posted this. This function can get metadata MUCH faster than using many pg_Field*().

By : yohgaki

<?php
/*
* Function MetaData()
* This function can get metadata MUCH faster than using many pg_Field*().
*
* == Slow way ==
* $id = pg_Exec($this-link_id, "select * from $table");
* pg_FieldNames($id,$n) for getting field names.
* pg_FieldSize($id,$n) for getttig field sizes.
* and so on.
* This can be really slow when there are many rows in the table.
* Note: This method is still useful to get metadata from joined result.
*
* == Better way ==
* Get metadata from database's system catalogs.
*
* $Id: metadata.php,v 1.1 2001/03/16 01:58:05 www Exp $
*
*/

/////
// 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

// See PostgreSQL developer manual (www.postgresql.org) for system table spec.
// Get catalog data from system tables.
$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");

?>

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By Codewalkers

blog comments powered by Disqus