pg_meta_data

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

pg_meta_data Get meta data for table

Description

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

pg_meta_data() returns table definition for table_name as an array.

Parameters

connection

An PgSql\Connection instance.

table_name

The name of the table.

extended

Flag for returning extended meta data. Default to false.

Return Values

An array of the table definition, or false on failure.

Changelog

Version Description
8.1.0 The connection parameter expects an PgSql\Connection instance now; previously, a resource was expected.

Examples

Example #1 Getting table metadata

<?php
$dbconn
= pg_connect("dbname=publisher") or die("Could not connect");

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

The above example will output:

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)
}
}

See Also

  • pg_convert() - Convert associative array values into forms suitable for SQL statements

add a note

User Contributed Notes 6 notes

up
2
yarnofmoo at gmail dot com
17 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
5 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
7 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
21 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
19 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