Public Synonyms?!?!

Moderator: NorbertKrupa

Post Reply
User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

Public Synonyms?!?!

Post by Jbaskin » Sat Jan 28, 2012 1:25 pm

Does vertica support public synonyms (like Oracle). Users are complaining that they always have to prefix a table name with the schema?

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

Re: Public Synonyms?!?!

Post by JimKnicely » Mon Jan 30, 2012 2:09 pm

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.

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)
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:

Code: Select all

ConnSettings=SET SEARCH_PATH TO NewSchema,$USER,PUBLIC
Jim Knicely

Image

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

Post Reply

Return to “New to Vertica”