vsql command + SEARCH_PATH

Moderator: NorbertKrupa

Post Reply
VerticalMan
Newbie
Newbie
Posts: 8
Joined: Thu Jul 25, 2013 10:47 am

vsql command + SEARCH_PATH

Post by VerticalMan » Fri Jul 26, 2013 8:23 am

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!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: vsql command + SEARCH_PATH

Post by JimKnicely » Fri Jul 26, 2013 3:31 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

VerticalMan
Newbie
Newbie
Posts: 8
Joined: Thu Jul 25, 2013 10:47 am

Re: vsql command + SEARCH_PATH

Post by VerticalMan » Mon Jul 29, 2013 11:41 am

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: vsql command + SEARCH_PATH

Post by JimKnicely » Mon Jul 29, 2013 1:23 pm

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.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

VerticalMan
Newbie
Newbie
Posts: 8
Joined: Thu Jul 25, 2013 10:47 am

Re: vsql command + SEARCH_PATH

Post by VerticalMan » Mon Jul 29, 2013 2:12 pm

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

VerticalMan
Newbie
Newbie
Posts: 8
Joined: Thu Jul 25, 2013 10:47 am

Re: vsql command + SEARCH_PATH

Post by VerticalMan » Wed Jul 31, 2013 5:32 pm

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?

Post Reply

Return to “Vertica Database Development”