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