CODEWITHSUNDEEP
phppostgresql
DevonDahon
DevonDahon

Reputation: 8350

How to list PostgreSQL columns (with name, type and description) by schema and table?

I'm using the php code below to get a tree structure of my PosgreSQL database in json format, like this.

{
  "foo": {
    "id":"int4"
    "name":"varchar",
  },
  "bar": {
    "id":"int4"
    "label":"varchar",
  }
}

However, the tables schema are mixed and I need to get the columns description as well, like this:

{
  "public": {
    "foo": {
      "id": { 
        "type": "int4",
        "description": "Lorem ipsum..."
      },
      "name": { 
        "type": "varchar",
        "description": "Hello World"
      },
    },
  },
  "custom": {
    "bar": {
      "id": { 
        "type": "int4",
        "description": "Id of my column..."
      },
      "label": { 
        "type": "varchar",
        "description": "Label description..."
      },
    }
  }
}

The code I'm currently using:

$conn = pg_connect("host={$host} port=5432 dbname={$db} user={$user} password={$pass}");

$sql = <<<SQL
SELECT tables.table_name, columns.column_name, columns.data_type, columns.udt_name
FROM information_schema.tables AS tables
    JOIN information_schema.columns AS columns
        ON tables.table_name = columns.table_name
WHERE tables.table_type = 'BASE TABLE'
AND tables.table_schema NOT IN
('pg_catalog', 'information_schema');
SQL;

$result = pg_query($conn, $sql);
$table_meta = new stdClass;
while ($row = pg_fetch_object($result)) {
    if (!isset($table_meta->{$row->table_name})) {
        $table_meta->{$row->table_name} = new stdClass;
    }
    $table_meta->{$row->table_name}->{$row->column_name} = $row->udt_name;
}

$table_json = json_encode($table_meta);

echo $table_json;

How can I improve this code to add table schema and column description ?

Upvotes: 0

Views: 579

Answers (2)

DevonDahon
DevonDahon

Reputation: 8350

For a full answer to this question, based on the code given by @Alan Tishin:

<?php

$host = 'localhost';
$db = 'your-database';
$user = 'your-user';
$pass = '***';

$conn = pg_connect("host={$host} port=5432 dbname={$db} user={$user} password={$pass}");

$sql = <<<SQL
SELECT
 c.ordinal_position,
 c.table_schema,
 c.table_name,
 c.column_name,
 pgd.description,
 c.udt_name as udt_name

FROM pg_catalog.pg_statio_all_tables as st

LEFT JOIN information_schema.columns as c
ON c.table_schema=st.schemaname and c.table_name=st.relname

LEFT JOIN pg_catalog.pg_description as pgd
ON pgd.objsubid=c.ordinal_position and  pgd.objoid=st.relid

WHERE 
 st.schemaname NOT IN ('pg_catalog', 'information_schema');
SQL;

$res = pg_query($conn, $sql);

$table = [];

while ($row = pg_fetch_object($res)) {
    $table[$row->table_schema][$row->table_name][$row->column_name] = '[' . $row->udt_name . '] ' . $row->description;
}

$table_json = json_encode($table);
echo $table_json;

Upvotes: 0

Alan Millirud
Alan Millirud

Reputation: 1172

is this you are looking for?

pgd.description is column comment

SELECT
 c.ordinal_position,
 c.table_schema,
 c.table_name,
 c.column_name,
 pgd.description,
 c.data_type,
 c.udt_name as udt_name

FROM pg_catalog.pg_statio_all_tables as st

LEFT JOIN information_schema.columns as c
ON c.table_schema=st.schemaname and c.table_name=st.relname

LEFT JOIN pg_catalog.pg_description as pgd
ON pgd.objsubid=c.ordinal_position and  pgd.objoid=st.relid


WHERE 
 st.schemaname NOT IN ('pg_catalog', 'information_schema')

Upvotes: 2

Related Questions