imitatio creatio co we łbie piszczy

postgres schemagrep function

From time to time, I need to "grep" database schema to locate some database objects.

One possibility is to dump the whole schema to file (pg_dump -s DBNAME) and use your text editor to browse it. It is quite handy and I actually use it a lot.

But sometimes, it is more convenient to have this "grep-like" possibility directly from psql.

So... here it is, the "schemagrep" function.
Please note that argument for this function is a regex pattern, so you can look for almost everything.
The search is case-insensitive.

begin;

create or replace function schemagrep_relkind( "char" ) returns text as $$
select case $1
    when 'r' then 'TABLE'
    when 'i' then 'INDEX'
    when 'S' then 'SEQUENCE'
    when 'v' then 'VIEW'
    when 'c' then 'COMPOSITE TYPE'
    when 't' then 'TOAST TABLE'
end
$$ language sql immutable strict;

drop function if exists schemagrep( text );
create function schemagrep( text ) returns table(
    schema name,
    object_name name,
    match_type text,
    psql_hint text
) as $$
select x.*, case when x.match_type ~ 'FUNCTION' then E'\\df+ ' when x.match_type ~ 'COMMENT' then E'\\d+ ' else E'\\d ' end || nspname || '.' || relname
from (
    select n.nspname, c.relname, schemagrep_relkind(c.relkind) || ' NAME' as match_type
    from pg_class c, pg_namespace n where c.relname ~* $1 and c.relnamespace = n.oid
  union all
    select distinct n.nspname, c.relname, schemagrep_relkind(c.relkind) || ' ATTRIBUTE'
    from pg_class c, pg_attribute a, pg_namespace n where a.attrelid = c.oid and c.relkind <> 'v' and a.attname ~* $1 and c.relnamespace = n.oid
  union all
    select n.nspname, proname, 'FUNCTION DEFINITION'
    from pg_proc, pg_namespace n where prosrc ~* $1 and pronamespace = n.oid
  union all
    select schemaname, viewname, 'VIEW DEFINITION'
    from pg_views where definition ~* $1
  union all
    select n.nspname, relname, 'CLASS CONSTRAINT'
    from pg_class c, pg_constraint cx, pg_namespace n where cx.conrelid = c.oid and cx.consrc ~* $1 and relnamespace = n.oid
  union all
    select distinct n.nspname, c.relname, 'COMMENT'
    from pg_class c, pg_description d, pg_namespace n where d.objoid = c.oid and d.description ~* $1 and c.relnamespace = n.oid
) x
$$ language sql stable strict;

grant execute on function schemagrep(text) to public;

commit;

Test it by running

SELECT * FROM schemagrep( 'anything' );


For me, it works like this:

testdb=# select * from schemagrep('account');
 schema |      object_name      |     match_type      |            psql_hint
--------+-----------------------+---------------------+----------------------------------
 public | pgbench_accounts      | TABLE NAME          | \d public.pgbench_accounts
 public | pgbench_accounts_pkey | INDEX NAME          | \d public.pgbench_accounts_pkey
 public | sp_acc_createaccount  | FUNCTION DEFINITION | \df+ public.sp_acc_createaccount
(3 rows)

That's it! Of course the function can be extended or modified to your needs, because it's in pure SQL :-)