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!


    NEW! Webcast: What is new in Viper 2 for developers?

    Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
    FREE! Go There Now!


    NEW! Section 508 of the U.S. Rehabilitation Act: Web accessibility compliance

    Because access to government information continues to be an area of concern for many U.S. citizens with disabilities, the U.S. government enacted Section 508 of the Rehabilitation Act in 2001 to ensure that government agencies create accessible Web content, enabling all citizens to access the information they need. A fully accessible Web site makes Web content accessible to all individuals, including those with disabilities, who may be accessing Web content via a variety of user agents. Common user agents include standard Web browsers, text-only browsers, assistive devices and mobile devices such as cell phones or personal digital assistants (PDAs).
    FREE! Go There Now!


    Role of Integrated Requirements Management in Software Delivery

    As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
    FREE! Go There Now!


    NEW! IBM Rational AppScan Standard Edition V7.7

    Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
    FREE! Go There Now!


    NEW! IBM – Taking Web 2.0 to Work

    David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
    FREE! Go There Now!


    NEW! Try the IBM SOA Sandbox for Process

    Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly.
    FREE! Go There Now!


    NEW! Project and Portfolio Management Executive Resource Kit

    Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle.
    FREE! Go There Now!


    NEW! Did you say mainframe? e-kit

    Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise.
    FREE! Go There Now!


    Build Forge Express demo: Enabling software delivery excellence for small and midsized businesses

    This demonstration gives you an overview of IBM® Rational® Build Forge Express Edition, a global offering that provides a framework to automate and execute software processes. Rational Build Forge provides a software assembly line that can support all of your tools, technologies, and platforms so you can achieve a repeatable, reliable, and traceable build and release process.
    FREE! Go There Now!


    NEW! Download IBM WebSphere Portal V6.1 beta code

    Download the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization.
    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 4 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek