timescale/pg-aiguide / skills/schema-exploration
schema-exploration
VerifiedExplore an existing PostgreSQL database before answering questions about its data or writing SQL. Use this skill whenever a user asks for a query or a data-backed answer against an unfamiliar schema (counts, missing or failed records, recent changes), asks where a business concept lives, or asks how tables, joins, views, routines, triggers, RLS, or extensions work. Find the relevant objects with read-only pg_catalog queries, then request approval before inspecting data-derived statistics or rows. Not a schema-design or migration guide.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 4.3 KB |
| references/data-values.md | markdown | 5.7 KB |
| references/extensions.md | markdown | 1.8 KB |
| references/foreign-tables.md | markdown | 3.9 KB |
| references/overview.md | markdown | 5.9 KB |
| references/partitioning-and-inheritance.md | markdown | 6.7 KB |
| references/psql.md | markdown | 2.1 KB |
| references/query-authoring.md | markdown | 2.1 KB |
| references/routines.md | markdown | 2.9 KB |
| references/security.md | markdown | 2.5 KB |
| references/tables.md | markdown | 7.7 KB |
| references/triggers.md | markdown | 1.6 KB |
| references/views.md | markdown | 2.4 KB |
references/routines.md
View on GitHub# Functions and procedures
Never execute a routine just to learn what it does. Start with a schema and name from [overview](overview.md). Overloads share a name: identify the correct signature before attributing behavior. Both queries use `$1` = schema name, `$2` = routine name. PostgreSQL 18 target; these catalog fields are present in PostgreSQL 14–17.
## Signatures, properties, and definitions
```sql
select
p.oid
, p.proname as routine_name
, pg_catalog.pg_get_function_identity_arguments(p.oid) as identity_arguments
, pg_catalog.pg_get_function_arguments(p.oid) as declared_arguments
, pg_catalog.pg_get_function_result(p.oid) as result_type
, case p.prokind when 'p' then 'procedure' else 'function' end as kind
, l.lanname as language
, p.provolatile as volatility
, p.proparallel as parallel_safety
, p.prosecdef as security_definer
, p.proconfig as settings
, p.proacl as explicit_acl
, pg_catalog.has_function_privilege(p.oid, 'EXECUTE') as current_role_can_execute
, pg_catalog.obj_description(p.oid, 'pg_proc') as comment
, pg_catalog.pg_get_functiondef(p.oid) as definition
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n on n.oid = p.pronamespace
join pg_catalog.pg_language l on l.oid = p.prolang
where n.nspname = $1
and p.proname = $2
and p.prokind in ('f', 'p')
order by identity_arguments
;
```
`provolatile`: `i` immutable, `s` stable, `v` volatile; these are planner promises, not proof a routine is safe. `proconfig` contains per-routine settings (notably `search_path`); NULL inherits caller settings. `proacl = NULL` means default privileges, **not** no access. `SECURITY DEFINER` changes whose privileges apply; do not assume it is safe without checking source and search path. For non-SQL or restricted-language routines, definitions may not reveal the actual implementation. The current role may not be able to inspect every routine.
## Known dependents of a particular overload
`$1` = schema, `$2` = name, `$3` = identity argument string returned above. Dependent objects can include triggers, rewrite rules, defaults and SQL-standard-body routines.
```sql
select
pg_catalog.pg_identify_object(d.classid, d.objid, d.objsubid) as dependent
, d.deptype as dependency_type
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n on n.oid = p.pronamespace
join pg_catalog.pg_depend d on d.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
and d.refobjid = p.oid
where n.nspname = $1
and p.proname = $2
and pg_catalog.pg_get_function_identity_arguments(p.oid) = $3
order by d.classid, d.objid, d.objsubid
;
```
Catalog dependencies are incomplete for PL/pgSQL bodies and dynamic SQL. No dependents does not mean unused: application code may call a function directly. For trigger callers, continue with [triggers](triggers.md). For view callers, continue with [views](views.md). Interpret extension-owned routines in context of [extensions](extensions.md).
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |