Convert a postgres db schema to a json format

Question

I am looking for a way to convert a postgres db schema to a JSON format data.

e.g.:

{"Table" : 
   {"name": "varchar(20)",
    "Age" : "int"
   },
}

Could someone help me with this?

Answer 1

Here is a solution using php:

$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;

---

This is as close as I can get using postgres 9.2.4

select row_to_json(table_schema)
from (
    select t.table_name, array_agg( c ) as columns
    from information_schema.tables t
    inner join (
        select cl.table_name, cl.column_name, cl.udt_name
        from information_schema.columns cl
    ) c (table_name,column_name,udt_name) on c.table_name = t.table_name
    where t.table_type = 'BASE TABLE'
    AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
    group by t.table_name
) table_schema;

The result is not pretty:

{"table_name":"users","columns":[
    {"table_name":"users","column_name":"user_id","udt_name":"int4"},
    {"table_name":"users","column_name":"user_email","udt_name":"varchar"}
]}

postgres 9.3 offers more json functions than 9.2. If possible upgrade and check out the 9.3 json function docs. If it's not possible to upgrade I would accomplish this with a script like the php code I posted earlier.