[php] PostgreSQL tabellen en kolomnamen overzicht

[Skaah]

Ik werk aan een database class, met een layout functie waar je in één klap een mooie array mee kan opvragen waarin beschreven is hoe je database eruit ziet.

Voor MySQL was dit een eitje met SHOW TABLES en EXPAIN table maar voor PostgreSQL kan ik zulke commando's niet vinden. Ik heb de SQL-commando lijst voor PostgreSQL doorgenomen, en tevens de PHP functie lijst.

Ik zou alle tabelnamen kunnen bruteforcen en de kolommen uit een SELECT * FROM table LIMIT 1 maar dat moet toch handiger kunnen?

Ik weet dat je met het \dt commando in de psql cmdline de tabellen kan opvragen, maar dat kun je toch niet als query sturen / met een resultset terug laten komen?

Voor de geïntresseerden staat hier de hele class (inc. documentatie) online.

[Suepahfly]

pg_field_name ?
pg_meta_data ?

Een paar wilde gokken moet ik zeggen, ik heb nog nooit met postgre gewerkt

[ Voor 26% gewijzigd door Suepahfly op 23-02-2004 18:15 ]

[ACM]

Start psql es met psql -E en kijk wat \dt voor query genereert, die kan je zelf ook gebruiken in je applicatie. 't Zijn niet zo mooie, simpele commando's als die voor mysql, maar wel enorm veel flexibeler

[WormLord]

In de tabel pg_class vind je de tabellen, views en dergelijke.
Tabel pg_attribute bevat de kolom definities.
En in pg_type kun je dan weer datatype informatie vinden.

Er zijn nog meer systeem tabellen (herkenbaar aan pg_), maar volgens mij kom je met de drie hierboven al een heel eind. Je zal zelf wel ff moeten uitzoeken hoe ze gekoppeld zijn, want dat weet nu even ik niet.

Je kan ook eens kijken naar phppgadmin. Hier staan genoeg queries in die doen wat jij wil .

[Skaah]

Tabellen lijst:

SQL:

psql => select relname as table from pg_class where relname not like 'pg_%' and 
relname not like '%_seq' and relname not like '%_pkey' and relname not like '%_key';

[jochemd]

Skaah schreef op 23 februari 2004 @ 17:34:
Ik werk aan een database class, met een layout functie waar je in één klap een mooie array mee kan opvragen waarin beschreven is hoe je database eruit ziet.

Voor MySQL was dit een eitje met SHOW TABLES en EXPAIN table maar voor PostgreSQL kan ik zulke commando's niet vinden.

Daar zijn ook geen speciale commando's voor.

Je aanname dat omdat MySQL het op een bepaalde manier oplost PostgreSQL dat ook doet is onjuist. PostgreSQL implementeert gewoon de SQL standaard en heeft alle informatie dus in zelfbeschrijvend formaat in het INFORMATION_SCHEMA staan. Wat dat is kan je in elk goed boek over SQL vinden.

[Skaah]

Google is mijn vriend:
http://archives.postgresq...eral/2002-02/msg01401.php
Edit:
De uiteindelijke code:

PHP:

        function db_layout()
        {       /*------------------------------------------------------------\
                | db_layout()                                                 |
                |=============================================================|
                | Creates an array containing the layout (tables, columns) of |
                | the current database.                                       |
                |=============================================================|
                | Returns:                                                    |
                |  (array) containing the layout. The keys of the array are   |
                |  table names, their values are associative arrays containing|
                |  columnname and columntype.                                 |
                |=============================================================|
                | Updates / Changelog                                         |
                |  + 2004-02-23 Initial release.                              |
                |  + 2004-02-26 Added support for pgsql.                      |
                \____________________________________________________________*/
                $tables = array(); $shadow = array();
                switch($this->provider)
                {
                        case 'mysql':
                                $result = $this->db_select('SHOW TABLES');
                                while($row = array_shift($result))
                                {
                                        $shadow[] = $row[0]; // build tablelist
                                }
                                foreach($shadow as $tablename)
                                {
                                        $result = $this->db_select('EXPLAIN '.$tablename);
                                        foreach($result as $r)
                                        {
                                                $tables[$tablename][$r['Field']] = $r['Type'];
                                        }
                                }
                                break;
                        case 'pgsql':
                                $result = $this->db_select('SELECT tablename FROM pg_tables WHERE tablename !~* \'pg_*\'');
                                while($row = array_shift($result))
                                {
                                        $shadow[] = $row['tablename']; // build tablelist
                                }
                                foreach($shadow as $tablename)
                                {
                                        $query = 'SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS length_var, a.attnotnull AS not_null, a.atthasdef as has_default FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = \''.$tablename.'\' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;';
                                        $result = $this->db_select($query);
                                        foreach($result as $r)
                                        {
                                                $tables[$tablename][$r['field']] = $r['type'];
                                        }
                                }
                                break;
                        default:
                                trigger_error('dbClass: db_layout(): provider not supported',E_USER_WARNING);
                                break;
                }
                return $tables;
        }

[ Voor 100% gewijzigd door Skaah op 26-02-2004 12:37 ]

[ACM]

't Is inderdaad nog iets netter om de verschillende views van het information_schema te gebruiken. Maar het is wel pas in recentere postgresql-versies beschikbaar, dus opzich niet zo vreemd dat men nog niet echt van het bestaan op de hoogte is.
De view information_schema.tables is waarschijnlijk wel interessant voor je

[JaQ]

jochemd schreef op 24 februari 2004 @ 18:03:
[...]
Daar zijn ook geen speciale commando's voor.

niet? volgens mij kom je met de shorthands \dt (voor tabellen) \d <<object>> (voor beschrijving van object) etc. een heel eind... maar misschien ligt dat aan mij...

[ACM]

DrFrankenstoner schreef op 26 februari 2004 @ 12:26:
niet? volgens mij kom je met de shorthands \dt (voor tabellen) \d <<object>> (voor beschrijving van object) etc. een heel eind... maar misschien ligt dat aan mij...

In psql wel ja

Skaah: als je een 7.3 of 7.4 postgres hebt is het gebruik van het information_schema handiger denk ik?

[ Voor 3% gewijzigd door ACM op 26-02-2004 16:18 ]