Basic usage

- « Basic usage

- PostgreSQL Functions »

- PHP Manual
- Examples
- Basic usage

## Basic usage

These examples contain user defined functions similar to legacy MySQL functions.

Example #1 PostgreSQL user defined functions example

    <?php
    // This function should be needed, since PostgreSQL connection binds database.
    function pg_list_dbs($db)
    {
        assert(is_resource($db));
        $query = '
    SELECT
     d.datname as "Name",
     u.usename as "Owner",
     pg_encoding_to_char(d.encoding) as "Encoding"
    FROM
     pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid
    ORDER BY 1;
    ';
        return pg_query($db, $query);
    }

    // List tables.
    function pg_list_tables($db)
    {
        assert(is_resource($db));
        $query = "
    SELECT
     c.relname as \"Name\",
     CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as \"Type\",
      u.usename as \"Owner\"
    FROM
     pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
    WHERE
     c.relkind IN ('r','v','S','')
     AND c.relname !~ '^pg_'
    ORDER BY 1;
    ";
        return pg_query($db, $query);
    }

    // See also pg_meta_data(). It returns field definition as array.
    function pg_list_fields($db, $table)
    {
        assert(is_resource($db));
        $query = "
    SELECT
     a.attname,
     format_type(a.atttypid, a.atttypmod),
     a.attnotnull,
     a.atthasdef,
     a.attnum
    FROM
     pg_class c,
     pg_attribute a
    WHERE
     c.relname = '".$table."'
     AND a.attnum > 0 AND a.attrelid = c.oid
    ORDER BY a.attnum;
    ";
        return pg_query($db, $query);
    }
    ?>