some helpful postgres queries regarding schemas and stuff

postgres_information_schema_stuff.sql

-- show all schema information
select * from information_schema.schemata;


-- find which schema a table is in
select 
	table_schema 
from 
	information_schema.tables
where
	table_name = 'some_table_name';
	
-- find a table that you kind of know the name of
select 
	table_schema,
	table_name
from 
	information_schema.tables
where
	table_name like '%maybe_has_this_string%';
	
-- describe a table and its columns (\d+)
select
	column_name,
	data_type
from
	information_schema.columns
where
	table_name = 'some_table_name';

-- find a table that might contain a column you think might exist
select
	table_name,
	column_name,
	data_type
from
	information_schema.columns
where
	column_name like '%column_name_might_contain_this%';
	
-- show grants
select * from information_schema.role_table_grants where grantee = 'my_user_name';

-- view stored proc definition
SELECT prosrc FROM pg_proc WHERE proname = 'function_name';

-- get definition of a view
select pg_get_viewdef('view_name')
-- or
select definition from pg_views where viewname = 'view_name';