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
 developerWorks - FREE Tools!
|
The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times.
FREE! Go There Now!
|
|
|
|
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial of the latest release of IBM Lotus Sametime Standard V8.0. Lotus Sametime Standard V8.0 is a platform for unified communications and collaboration that combines security features with an extensible, open solution including integrated Voice over IP, geographic location awareness, mobile clients, and a robust Business Partner community offering telephony and video integration.
FREE! Go There Now!
|
|
|
|
This Fall, IBM Rational talks to you directly through a special teleconference series giving you access to the best minds in IBM Rational - product experts and market thought leaders who will answer your questions during these pre-scheduled telephone conference calls. Register today!
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
|
|
|
|
As organizations have grown increasingly dependent on online software, the risk of malicious attacks has also become far more serious. Fortunately, well-governed organizations can protect their Web applications by injecting vulnerability assessments and ethical hacks into their software development and delivery processes. This paper describes 12 of the most common hacker attacks and provides basic rules that you can follow to help create more hack-resistant Web applications.
FREE! Go There Now!
|
|
|
|
This paper is about the critical role that a discipline called integrated requirements management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrating, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way.
FREE! Go There Now!
|
|
|
|
In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
FREE! Go There Now!
|
|
|
|
Join this webcast to learn how IBM Rational's Functional Testing solution enables you to implement automation your way, at your pace, with your existing staff. In this webcast, you’ll learn how you can eliminate redundancy of manual test scripts, reduce errors, and increase test coverage through test automation. After this presentation you will understand how IBM Rational Functional Testing solution can streamline your manual testing and make test automation easily attainable.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
