Public Synonyms?!?!
Moderator: NorbertKrupa
Public Synonyms?!?!
Does vertica support public synonyms (like Oracle). Users are complaining that they always have to prefix a table name with the schema?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Public Synonyms?!?!
Vertica does not have public synonyms like Oracle. However, you can set a variable called search_path that lists in order the schemas Vertica will try to resolve the full path to the object.
Example:
I am logged into Vertica via vsql as the vertica_user1 user. I want to get a table row count from a table named date_dim which is in the main_wh schema without specifying the schema name.
The set search_path is a per session setting. Once you log out it'll reset.
You can make it permanent by putting the set command in a file named .vsqlrc in the user's home directory. vsql executes any commands in this file before starting.
ODBC connections can also use the set command. Here you would simply add the set command to the ODBC.ini file with the following:
Example:
I am logged into Vertica via vsql as the vertica_user1 user. I want to get a table row count from a table named date_dim which is in the main_wh schema without specifying the schema name.
Code: Select all
vertica_user1=> select count(*) from date_dim;
ERROR: relation "date_dim" does not exist
vertica_user1=> show search_path;
name | setting
-------------+---------------------------------------------------
search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)
vertica_user1=> set search_path = main_wh, "$user", public, v_catalog, v_monitor, v_internal;
SET
vertica_user1=> select count(*) from date_dim;
count
-------
2194
(1 row)
You can make it permanent by putting the set command in a file named .vsqlrc in the user's home directory. vsql executes any commands in this file before starting.
ODBC connections can also use the set command. Here you would simply add the set command to the ODBC.ini file with the following:
Code: Select all
ConnSettings=SET SEARCH_PATH TO NewSchema,$USER,PUBLIC
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.