How to set a schema name or search_path

[emilioplatzer]

I want to know where can I set the schema name. Of course I can write the schema name in each sentence. But in my organization we do not do that. We set the schema in a set search_path sentence and then write commands without writing the schema name explicitily.

I try:

• PGSQL Extension Version: 0.2.0
• VSCode Version: 1.39.2
• OS Version: Windows 10

Steps to Reproduce:

1. In postgresql (in pgAdmin4 or psql)

   create database my_test_db;
   create schema mtest;
   set search_path = mtest;
   create table the_table(
   my_field text
   ); -- it is created in mtest schema
   insert into the_table values ('the value');

2. In Visual Studio Code settings.json

   "pgsql.connections": [
       {
           "host": "localhost",
           "dbname": "my_test_db",
           "user": "postgres",
           "password": "",
           "emptyPasswordInput": false,
           "port": "5432",
           "profileName": "my-profile",
           "options": "search_path = mtest",
           "connectTimeout": 15,
           "encrypt": false,
           "applicationName": "vscode-pgsql",
           "hostaddr": "",
           "sslmode": "prefer",
           "clientEncoding": "",
           "sslcompression": true,
           "sslcert": "",
           "sslkey": "",
           "sslrootcert": "",
           "sslcrl": "",
           "requirepeer": ""
       }
   ]

3. try to connect

I get:

PostgreSQL: Failed to connect: FATAL: argumentos de línea de órdenes no válidos para proceso servidor: search_path=mtest

I try with other "options" values. For example "schema=mtest", "schema:mtest", "search_path:mtest", etc.

[ababra]

Agree that this would be helpful to have. As a workaround, I had to create a separate database user and set the user's default search path to the app's schema:

DROP ROLE IF EXISTS app_user;
CREATE ROLE app_user WITH INHERIT LOGIN PASSWORD NULL;
GRANT CREATE ON DATABASE db_name TO app_user;
ALTER ROLE app_user WITH PASSWORD 'PASSWORD';
-- Sets app user's default search path to app's schema
ALTER ROLE app_user SET search_path TO "app_schema","public";