[Feature] Add support for multiple schemas besides `public`

[ataki]

Issue Type

• [ x ] Feature Request
• [ ] Bugfix/Errors
• [ ] Questions
• [ ] Other

Description

Currently, the table schema feature shows only the public namespace for postgres. I'm using it to connect to our Redshift warehouse (which has a postgres interface), which contains multiple namespaced tables (e.g. public.*, analytics.*, temp.*, etc). I'd love to be able to use the table schema feature to search across all namespaces, and autocomplete based on schema.

Behavior: When I press CTRL+E,D, I should be able to see public.users as well as analytics.kpis in the dropdown menu.

[tkopets]

Multiple schemas are supported - you just have to list them in your search path.

By default for PostgreSQL we include only those tables/views/columns that are visible by your postgresql setting search_path. I assume you have only public schema in your search path. I understand this is debatable, but there are a number of use cases when it's not desirable to pull objects in all schemas - some users have lot's of schemas that contain similar objects (e.g. multitenant DB with tenant per schema). To achieve what you are looking for you have 2 options:

1. Edit your DB or user default search path. e.g. https://stackoverflow.com/questions/2875610/permanently-set-postgresql-schema-path
2. Edit your SQLTools.sublime-settings file and customize the queries that return the list of objects for SQLTools so they return all objects in all schemas. However, please note that by doing so you will override the default queries (which are sometimes changed between releases).

   {
   "cli_options": {
       "pgsql": {
           "queries": {
               "desc" : {
                   "query": "select '|' || quote_ident(table_schema)||'.'||quote_ident(table_name) ||'|' as tblname from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema') order by table_schema = current_schema() desc, table_schema, table_name"
               },
               "columns": {
                   "query": "select '|' || quote_ident(table_name) || '.' || quote_ident(column_name) || '|' from information_schema.columns where table_schema not in ('pg_catalog', 'information_schema') order by table_name, ordinal_position"
               },
               "functions": {
                   "query": "select '|' || quote_ident(n.nspname)||'.'||quote_ident(f.proname) || '(' || pg_get_function_identity_arguments(f.oid) || ')' || '|' as funname from pg_catalog.pg_proc as f inner join pg_catalog.pg_namespace as n on n.oid = f.pronamespace where f.proisagg = false and n.nspname not in ('pg_catalog', 'information_schema')"
               },
           }
       }
   }
   }

[ataki]

Awesome, that does the job. Thanks!