Skip to content

Commit 1a19cac

Browse files
srividyareddy786Yuan325
srividyareddy786
and
Yuan325
authored
feat(tools/postgres-list-schemas): add new postgres-list-schemas tool (googleapis#1741)
## Description Add a read-only PostgreSQL custom list_schemas tool, that returns the schemas present in the database excluding system and temporary schemas. Returns the schema name, schema owner, grants, number of functions, number of tables, and number of views within each schema. <img width="1985" height="1043" alt="Screenshot 2025-10-20 at 7 45 45 PM" src="https://github.com/user-attachments/assets/8c4f0bb8-587c-489a-8795-efa79e92b06f" /> <img width="3372" height="1694" alt="3NpZG7W6h3XGsM7" src="https://github.com/user-attachments/assets/370b5440-cc48-4c4e-82ea-4fd508cbcf2b" /> > Should include a concise description of the changes (bug or feature), it's > impact, along with a summary of the solution ## PR Checklist > Thank you for opening a Pull Request! Before submitting your PR, there are a > few things you can do to make sure it goes smoothly: - [x] Make sure you reviewed [CONTRIBUTING.md](https://github.com/googleapis/genai-toolbox/blob/main/CONTRIBUTING.md) - [x] Make sure to open an issue as a [bug/issue](https://github.com/googleapis/genai-toolbox/issues/new/choose) before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea - [x] Ensure the tests and linter pass - [x] Code coverage does not decrease (if any source code was changed) - [x] Appropriate docs were updated (if necessary) - [x] Make sure to add `!` if this involve a breaking change 🛠️ Fixes #<issue_number_goes_here> Co-authored-by: Yuan Teoh <45984206+Yuan325@users.noreply.github.com>
1 parent 5367285 commit 1a19cac

17 files changed

Lines changed: 515 additions & 3 deletions

File tree

‎cmd/root.go‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -160,6 +160,7 @@ import (
160160
_ "github.com/googleapis/genai-toolbox/internal/tools/postgres/postgreslistactivequeries"
161161
_ "github.com/googleapis/genai-toolbox/internal/tools/postgres/postgreslistavailableextensions"
162162
_ "github.com/googleapis/genai-toolbox/internal/tools/postgres/postgreslistinstalledextensions"
163+
_ "github.com/googleapis/genai-toolbox/internal/tools/postgres/postgreslistschemas"
163164
_ "github.com/googleapis/genai-toolbox/internal/tools/postgres/postgreslisttables"
164165
_ "github.com/googleapis/genai-toolbox/internal/tools/postgres/postgreslistviews"
165166
_ "github.com/googleapis/genai-toolbox/internal/tools/postgres/postgressql"

‎cmd/root_test.go‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1404,7 +1404,7 @@ func TestPrebuiltTools(t *testing.T) {
14041404
wantToolset: server.ToolsetConfigs{
14051405
"alloydb_postgres_database_tools": tools.ToolsetConfig{
14061406
Name: "alloydb_postgres_database_tools",
1407-
ToolNames: []string{"execute_sql", "list_tables", "list_active_queries", "list_available_extensions", "list_installed_extensions", "list_autovacuum_configurations", "list_memory_configurations", "list_top_bloated_tables", "list_replication_slots", "list_invalid_indexes", "get_query_plan", "list_views"},
1407+
ToolNames: []string{"execute_sql", "list_tables", "list_active_queries", "list_available_extensions", "list_installed_extensions", "list_autovacuum_configurations", "list_memory_configurations", "list_top_bloated_tables", "list_replication_slots", "list_invalid_indexes", "get_query_plan", "list_views", "list_schemas"},
14081408
},
14091409
},
14101410
},
@@ -1434,7 +1434,7 @@ func TestPrebuiltTools(t *testing.T) {
14341434
wantToolset: server.ToolsetConfigs{
14351435
"cloud_sql_postgres_database_tools": tools.ToolsetConfig{
14361436
Name: "cloud_sql_postgres_database_tools",
1437-
ToolNames: []string{"execute_sql", "list_tables", "list_active_queries", "list_available_extensions", "list_installed_extensions", "list_autovacuum_configurations", "list_memory_configurations", "list_top_bloated_tables", "list_replication_slots", "list_invalid_indexes", "get_query_plan", "list_views"},
1437+
ToolNames: []string{"execute_sql", "list_tables", "list_active_queries", "list_available_extensions", "list_installed_extensions", "list_autovacuum_configurations", "list_memory_configurations", "list_top_bloated_tables", "list_replication_slots", "list_invalid_indexes", "get_query_plan", "list_views", "list_schemas"},
14381438
},
14391439
},
14401440
},
@@ -1534,7 +1534,7 @@ func TestPrebuiltTools(t *testing.T) {
15341534
wantToolset: server.ToolsetConfigs{
15351535
"postgres_database_tools": tools.ToolsetConfig{
15361536
Name: "postgres_database_tools",
1537-
ToolNames: []string{"execute_sql", "list_tables", "list_active_queries", "list_available_extensions", "list_installed_extensions", "list_autovacuum_configurations", "list_memory_configurations", "list_top_bloated_tables", "list_replication_slots", "list_invalid_indexes", "get_query_plan", "list_views"},
1537+
ToolNames: []string{"execute_sql", "list_tables", "list_active_queries", "list_available_extensions", "list_installed_extensions", "list_autovacuum_configurations", "list_memory_configurations", "list_top_bloated_tables", "list_replication_slots", "list_invalid_indexes", "get_query_plan", "list_views", "list_schemas"},
15381538
},
15391539
},
15401540
},

‎docs/en/reference/prebuilt-tools.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,7 @@ details on how to connect your AI tools (IDEs) to databases via Toolbox and MCP.
4545
* `get_query_plan`: Generate the execution plan of a statement.
4646
* `list_views`: Lists views in the database from pg_views with a default
4747
limit of 50 rows. Returns schemaname, viewname and the ownername.
48+
* `list_schemas`: Lists schemas in the database.
4849

4950
## AlloyDB Postgres Admin
5051

@@ -214,6 +215,7 @@ details on how to connect your AI tools (IDEs) to databases via Toolbox and MCP.
214215
* `get_query_plan`: Generate the execution plan of a statement.
215216
* `list_views`: Lists views in the database from pg_views with a default
216217
limit of 50 rows. Returns schemaname, viewname and the ownername.
218+
* `list_schemas`: Lists schemas in the database.
217219

218220
## Cloud SQL for PostgreSQL Observability
219221

@@ -509,6 +511,7 @@ details on how to connect your AI tools (IDEs) to databases via Toolbox and MCP.
509511
* `get_query_plan`: Generate the execution plan of a statement.
510512
* `list_views`: Lists views in the database from pg_views with a default
511513
limit of 50 rows. Returns schemaname, viewname and the ownername.
514+
* `list_schemas`: Lists schemas in the database.
512515

513516
## Google Cloud Serverless for Apache Spark
514517

‎docs/en/resources/sources/alloydb-pg.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,9 @@ cluster][alloydb-free-trial].
4848
- [`postgres-list-views`](../tools/postgres/postgres-list-views.md)
4949
List views in an AlloyDB for PostgreSQL database.
5050

51+
- [`postgres-list-schemas`](../tools/postgres/postgres-list-schemas.md)
52+
List schemas in an AlloyDB for PostgreSQL database.
53+
5154
### Pre-built Configurations
5255

5356
- [AlloyDB using MCP](https://googleapis.github.io/genai-toolbox/how-to/connect-ide/alloydb_pg_mcp/)

‎docs/en/resources/sources/cloud-sql-pg.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,9 @@ to a database by following these instructions][csql-pg-quickstart].
4444
- [`postgres-list-views`](../tools/postgres/postgres-list-views.md)
4545
List views in a PostgreSQL database.
4646

47+
- [`postgres-list-schemas`](../tools/postgres/postgres-list-schemas.md)
48+
List schemas in a PostgreSQL database.
49+
4750
### Pre-built Configurations
4851

4952
- [Cloud SQL for Postgres using

‎docs/en/resources/sources/postgres.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,9 @@ reputation for reliability, feature robustness, and performance.
3838
- [`postgres-list-views`](../tools/postgres/postgres-list-views.md)
3939
List views in a PostgreSQL database.
4040

41+
- [`postgres-list-schemas`](../tools/postgres/postgres-list-views.md)
42+
List schemas in a PostgreSQL database.
43+
4144
### Pre-built Configurations
4245

4346
- [PostgreSQL using MCP](https://googleapis.github.io/genai-toolbox/how-to/connect-ide/postgres_mcp/)

‎docs/en/resources/tools/postgres/postgres-list-schemas.md‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
---
2+
title: "postgres-list-schemas"
3+
type: docs
4+
weight: 1
5+
description: >
6+
The "postgres-list-schemas" tool lists user-defined schemas in a database.
7+
aliases:
8+
- /resources/tools/postgres-list-schemas
9+
---
10+
11+
## About
12+
13+
The `postgres-list-schemas` tool retrieves information about schemas in a database excluding system
14+
and temporary schemas. It's compatible with any of the following sources:
15+
16+
- [alloydb-postgres](../../sources/alloydb-pg.md)
17+
- [cloud-sql-postgres](../../sources/cloud-sql-pg.md)
18+
- [postgres](../../sources/postgres.md)
19+
20+
`postgres-list-schemas` lists detailed information as JSON for each schema. The tool takes the following
21+
input parameters:
22+
23+
- `schema_name` (optional): A pattern to filter schema names using SQL LIKE operator.
24+
If omitted, all user-defined schemas are returned.
25+
26+
## Example
27+
28+
```yaml
29+
tools:
30+
list_schemas:
31+
kind: postgres-list-schemas
32+
source: postgres-source
33+
description: "Lists all schemas in the database ordered by schema name and excluding system and temporary schemas. It returns the schema name, schema owner, grants, number of functions, number of tables and number of views within each schema."
34+
```
35+
36+
The response is a json array with the following elements:
37+
38+
```json
39+
{
40+
"schema_name": "name of the schema.",
41+
"owner": "role that owns the schema",
42+
"grants": "A JSON object detailing the privileges (e.g., USAGE, CREATE) granted to different roles or PUBLIC on the schema.",
43+
"tables": "The total count of tables within the schema",
44+
"views": "The total count of views within the schema",
45+
"functions": "The total count of functions",
46+
}
47+
```
48+
49+
## Reference
50+
51+
| **field** | **type** | **required** | **description** |
52+
|-------------|:--------:|:------------:|----------------------------------------------------|
53+
| kind | string | true | Must be "postgres-list-schemas". |
54+
| source | string | true | Name of the source the SQL should execute on. |
55+
| description | string | false | Description of the tool that is passed to the LLM. |

‎internal/prebuiltconfigs/tools/alloydb-postgres.yaml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,11 @@ tools:
159159
list_views:
160160
kind: postgres-list-views
161161
source: alloydb-pg-source
162+
163+
list_schemas:
164+
kind: postgres-list-schemas
165+
source: alloydb-pg-source
166+
162167
toolsets:
163168
alloydb_postgres_database_tools:
164169
- execute_sql
@@ -173,3 +178,4 @@ toolsets:
173178
- list_invalid_indexes
174179
- get_query_plan
175180
- list_views
181+
- list_schemas

‎internal/prebuiltconfigs/tools/cloud-sql-postgres.yaml‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,10 @@ tools:
159159
kind: postgres-list-views
160160
source: cloudsql-pg-source
161161

162+
list_schemas:
163+
kind: postgres-list-schemas
164+
source: cloudsql-pg-source
165+
162166
toolsets:
163167
cloud_sql_postgres_database_tools:
164168
- execute_sql
@@ -173,3 +177,4 @@ toolsets:
173177
- list_invalid_indexes
174178
- get_query_plan
175179
- list_views
180+
- list_schemas

‎internal/prebuiltconfigs/tools/postgres.yaml‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,10 @@ tools:
158158
kind: postgres-list-views
159159
source: postgresql-source
160160

161+
list_schemas:
162+
kind: postgres-list-schemas
163+
source: postgresql-source
164+
161165
toolsets:
162166
postgres_database_tools:
163167
- execute_sql
@@ -172,3 +176,4 @@ toolsets:
172176
- list_invalid_indexes
173177
- get_query_plan
174178
- list_views
179+
- list_schemas

0 commit comments

Comments
 (0)