vsql command + SEARCH_PATH

[VerticalMan]

Hello friends,

I'd like to know whether there is a proper way to specify search_path by default on vsql.
I'll tried with vsql ... -vSEARC_PATH="mySchema" but it does not work.

I know I can execute it from inside ("SET search_path [TO|=] mySchema"), but my script executes multiple commands I it is too much verbose.
Isn't any option like

vsql -Uuser -wpass -dDB -sSchema ??

This would be perfect.

I'd appreciate any help, thank you!

[JimKnicely]

Hi,

You can set the SEARCH_PATH for a user:

Code: Select all

dbadmin=> CREATE SCHEMA jim_schema;
CREATE SCHEMA

dbadmin=> CREATE USER jim;
CREATE USER

dbadmin=> ALTER USER jim SEARCH_PATH "$user", jim_schema, public, v_catalog, v_monitor, v_internal;
ALTER USER

dbadmin=> \c dbadmin jim
You are now connected to database "dbadmin" as user "jim".

dbadmin=> SHOW search_path;
    name     |                            setting
-------------+---------------------------------------------------------------
 search_path | "$user", jim_schema, public, v_catalog, v_monitor, v_internal
(1 row)

[VerticalMan]

Sorry it is not working for me. May you help me?

Code: Select all

VMart=> \du

      List of users
 User name | Is Superuser
-----------+--------------
 dbadmin   | t
(1 row)

Code: Select all

VMart=> ALTER USER dbadmin SEARCH_PATH temp;
ERROR 4856:  Syntax error at or near "SEARCH_PATH" at character 20
LINE 1: ALTER USER dbadmin SEARCH_PATH temp;

VMart=> SET SEARCH_PATH TO temp;
SET
VMart=> SHOW SEARCH_PATH;
    name     |                setting
-------------+----------------------------------------
 search_path | temp, v_catalog, v_monitor, v_internal
(1 row)

I also tried doing exactly the same as you, creating a new user.

If I spell it as ALTER USER X SET SEARCH_PATH TO temp:

Code: Select all

VMart=> ALTER USER tempuser SET SEARCH_PATH TO temp;
ERROR 2676:  Command ALTER USER is not supported

[JimKnicely]

Hi,

What version of Vertica are you running? This feature was introduced in Vertica 6.1.1.

From the New Fearures document:

New SEARCH_PATH User Setting

The default search path that Vertica uses to find tables and User Defined Function (UDF)s referenced without a schema can be set in 6.1 SP1 (Version 6.1.1) on a per-user basis. The CREATE USER and ALTER USER statements have a new SEARCH_PATH argument to set the list of schemas Vertica searches when it find an unqualified reference to a table or UDFs. See Setting Search Paths in the Administrator's Guide for more information

If you are running an older version, maybe you can use the ~/.vsqlrc file to run your SET command? Search the forums for .vsqlrc for some examples.

[VerticalMan]

You're right. My fault. 6.0.1...

[VerticalMan]

I solve it now I upgraded,
but anyway I think it would really be great if ALTERing USER did not need a connection restart.

It's bad for long and compelx scripts, for instance, where you need to reopen a connection everytime you change !

Any solution for changing it permanently dynamically and easily?