Database Code
  Home arrow Database Code arrow Better PostgreSQL Metadata Function
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
Download TestComplete 
Forums Sitemap 
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

Better PostgreSQL Metadata Function
By: Codewalkers
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 2
    2002-01-18

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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

     

    IBM® developerWorks developerWorks - FREE Tools!


    IBM DB2 Deep Compression ROI Tool

    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!


    NEW! Accelerating Software Innovation on i on Power Systems

    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!


    NEW! Evaluate IBM Lotus Sametime Standard V8.0

    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!


    NEW! Rational 'Talks to You' Teleconference Series

    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!


    NEW! Rational Talks to You: Manage RUP-based CMMI initiatives

    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!


    NEW! Rational Talks to You: Scott Ambler on being agile in a global development environment

    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!


    NEW! The dirty dozen: preventing common application-level hack attacks

    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!


    NEW! The role of integrated requirements management in software delivery

    This paper is about the critical role that a discipline called integrated require­ments management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrat­ing, 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!


    NEW! Webcast: Calling All Testers! Find Application Vulnerabilities Early in the Development Process Where they are Easier to Fix and Less Risky to your Business

    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!


    NEW! Webcast: Striking the right balance between manual and automated testing

    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!

    DATABASE CODE ARTICLES

    - Examples and Tools for Database Design
    - Relationships, Entities and Database Design
    - Modeling and Designing Databases
    - Data extract to Excel
    - Oracle database class 0.76
    - The opposite of mysql_fetch_assoc
    - On line Thermal Transmitance Calculation
    - pjjTextBase
    - PHP Object Generator
    - FastMySQL
    - RC4PHP
    - SQL function with integrated sprintf()
    - DB Interaction Classes v1.1
    - deeMySQLParser
    - CSV to SQL convertor





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 6 Hosted by Hostway
    Stay green...Green IT