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 ?

Answer

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

Advertisement

Answer