pg_meta_data

(PHP 4 >= 4.3.0, PHP 5, PHP 7, PHP 8)

pg_meta_dataLiefert die Metadaten einer Tabelle als Array

Beschreibung

pg_meta_data(PgSql\Connection $connection, string $table_name, bool $extended = false): array|false

pg_meta_data() gibt die Tabellendefinition für table_name in einem Array zurück.

Parameter-Liste

connection

Eine PgSql\Connection-Instanz.

table_name

Der Tabellenname.

extended

Das Flag, um erweiterte Metadaten zurückzugeben. Vorgabewert ist false.

Rückgabewerte

Ein Array, das die Tabellendefinition enthält. Bei einem Fehler wird false zurückgegeben.

Changelog

Version Beschreibung
8.1.0 Der Parameter connection erwartet nun eine PgSql\Connection-Instanz; vorher wurde eine Ressource erwartet.

Beispiele

Beispiel #1 Metadaten für eine Tabelle ermitteln

<?php
$dbconn
= pg_connect("dbname=publisher") or die("Verbindungsaufbau fehlgeschlagen");

$meta = pg_meta_data($dbconn, 'authors');
if (
is_array($meta)) {
echo
'<pre>';
var_dump($meta);
echo
'</pre>';
}
?>

Das oben gezeigte Beispiel erzeugt folgende Ausgabe:

array(3) {
["author"]=>
array(5) {
  ["num"]=>
  int(1)
  ["type"]=>
  string(7) "varchar"
  ["len"]=>
  int(-1)
  ["not null"]=>
  bool(false)
  ["has default"]=>
  bool(false)
}
["year"]=>
array(5) {
  ["num"]=>
  int(2)
  ["type"]=>
  string(4) "int2"
  ["len"]=>
  int(2)
  ["not null"]=>
  bool(false)
  ["has default"]=>
  bool(false)
}
["title"]=>
array(5) {
  ["num"]=>
  int(3)
  ["type"]=>
  string(7) "varchar"
  ["len"]=>
  int(-1)
  ["not null"]=>
  bool(false)
  ["has default"]=>
  bool(false)
}
}

Siehe auch

  • pg_convert() - Konvertiert die Werte eines assoziativen Arrays in die für SQL-Anweisungen passende Form

add a note add a note

User Contributed Notes 6 notes

up
2
yarnofmoo at gmail dot com
16 years ago
You can get some possibly more useful information with the query:

SELECT table_name, column_name, data_type, character_maximum_length FROM information_schema.columns WHERE table_name='tablename';
up
0
shaman_master at list dot ru
4 years ago
If parameter $extended not false:
<?php
array (size=2)
 
'name' =>
    array (
size=11)
     
'num' => int 1
     
'type' => string 'varchar' (length=7)
     
'len' => int -1
     
'not null' => boolean false
     
'has default' => boolean true
     
'array dims' => int 0
     
'is enum' => boolean false
     
'is base' => boolean true
     
'is composite' => boolean false
     
'is pesudo' => boolean false
     
'description' => string '' (length=0)
 
'id' =>
    array (
size=11)
     
'num' => int 2
     
'type' => string 'int4' (length=4)
     
'len' => int 4
     
'not null' => boolean true
     
'has default' => boolean true
     
'array dims' => int 0
     
'is enum' => boolean false
     
'is base' => boolean true
     
'is composite' => boolean false
     
'is pesudo' => boolean false
     
'description' => string '' (length=0)
?>
up
0
Hayley Watson
6 years ago
To specify a schema as well as a table name, use the "schemaname.tablename" form as usual for PostgreSQL and the other functions in this extension. Without the prefix, of course, the default schema search path is used.

<?php
  $meta
= pg_meta_data($dbconn, 'foo.bar'); // table "bar" in schema "foo"
 
if (is_array($meta)) {
     
var_dump($meta);
  }
?>
up
0
dmiller at NOSPAM dot judcom dot nsw dot gov dot au
20 years ago
This function seems to be case-sensitive on tablename (php-4.3.1)

The Array returned is of the following structure
['field name'] => Array
   (
     ['num'] => Field number starting at 1
     ['type'] => data type, eg varchar, int4
     ['len'] => internal storage size of field. -1 for varying
     ['not null'] => boolean
     ['has default'] => boolean
   )
     ......

for Varied size datatypes (varchar, text, etc)
you can get the max data length from the system table pg_attribute.atttypmod -4
eg.
select attnum, attname , atttypmod -4 as field_len
from pg_attribute, pg_class
where relname='$tablename'
and attrelid=relfilenode
and attnum>=1
up
-1
rburghol at vt dot edu
18 years ago
When querying on meta data from a temp table, the meta data seems to persist even if a fresh connection is established, where the temp table no longer exists.

For example, if you create a connection and a temp table like so:
$dbconn1 = pg_connect('blah blah', , PGSQL_CONNECT_FORCE_NEW);
pg_exec($dbconn1,'create temp table foo as select 'foo' as namecol, 'bar' as valcol');

Then create a new connection

$dbconn2 = pg_connect('blah blah', , PGSQL_CONNECT_FORCE_NEW);

And query the meta data for table 'foo' in this new connection, it will report the facts about this table:
pg_meta_data($dbconn2,'foo');

"'Array ( [foo] => Array ( [num] => 1 [type] => varchar... "

However, trying to remove this table:
pg_exec($dbconn,'drop table foo');

Throws an error:
pg_exec(): Query failed: ERROR: table "foo" does not exist in ...
up
-4
jsnell at e-normous dot com
16 years ago
The built in function does not provide any support for selecting a schema.  If you need schema support and do not want to alter your SEARCH_PATH, the following function can provide it:

function meta_data($table, $schema = 'public')
    {
$result = pg_query_params("SELECT a.attname, a.attnum, t.typname, a.attlen, a.attnotNULL, a.atthasdef, a.attndims
FROM pg_class as c, pg_attribute a, pg_type t, pg_namespace n
WHERE a.attnum > 0
AND a.attrelid = c.oid
AND c.relname = $1
AND a.atttypid = t.oid
AND n.oid = c.relnamespace
AND n.nspname = $2
ORDER BY a.attnum", array($table, $schema));
$fields = array();
while($row = pg_fetch_array()) {
$fields['attname'] = $row;
}
        return $fields;
    }
To Top