Re: public synonym - Mailing list pgsql-sql

You can infer the context by first setting the search_path variable.  You can set it initially in your connection or do
itfor a database context or even a role context<br /> SET search_path = MASTER_USER, public, pg_catalog;<br /><span>
</span>ALTERDATABASE whatever  SET search_path = MASTER_USER, public, pg_catalog;<br /> ALTER ROLE whoever SET
search_path= MASTER_USER, public, pg_catalog;<br /><br /> Then you can continue to let the tables be non-qualified.  
byebye synonyms!<br /><br /> Regards<br /> Michael<br /><blockquote
cite="mid:358282890.1765953.1452197860946.JavaMail.yahoo@mail.yahoo.com"style="border: 0px none;" type="cite"><div
class="__pbConvHr"style="margin:30px 25px 10px 25px;"><div style="width:100%;border-top:1px solid
#EDEEF0;padding-top:5px"><divstyle="display:inline-block;white-space:nowrap;vertical-align:middle;width:49%;"><a
href="mailto:eugeneymail@ymail.com"moz-do-not-send="true" style="color:#737F92 
 
!important;padding-right:6px;font-weight:bold;text-decoration:none 
!important;">Eugene Yin</a></div><div
style="display:inline-block;white-space:nowrap;vertical-align:middle;width:48%;text-align:right;"><font
color="#9FA2A5"><spanstyle="padding-left:6px">Thursday, January 7, 2016 3:17 PM</span></font></div></div></div><div
__pbrmquotes="true"class="__pbConvBody" style="color:#888888;margin-left:24px;margin-right:24px;"><div
style="color:#000;
 
background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, 
Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px"><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span
class=""id="yui_3_16_0_1_1452135740077_60638" style="font-family: Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);">PostgreSQLver 9.4.5.  Linux OS.</span></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class=""
style="font-family:Consolas,'Lucida Console', monospace; font-size: 12.8000001907349px; 
 
text-indent: -7px; background-color: rgb(240, 247, 255);">Application:  Web Based</span></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><spanclass="" style="font-family:Consolas, 'Lucida Console', monospace;
font-size:12.8000001907349px; 
 
text-indent: -7px; background-color: rgb(240, 247, 255);"><br /></span></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><spanclass="" id="yui_3_16_0_1_1452135740077_61042" style="font-family: 
 
Consolas, 'Lucida Console', monospace; font-size: 12.8000001907349px; 
text-indent: -7px; background-color: rgb(240, 247, 255);">Platform:  </span></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><spanclass="" id="yui_3_16_0_1_1452135740077_61044" style="font-family: 
 
Consolas, 'Lucida Console', monospace; font-size: 12.8000001907349px; 
text-indent: -7px; background-color: rgb(240, 247, 255);">    App Server (java) --> jdbc call --> Database Server
(PostgreSQL)</span></div><divdir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class="" style="font-family:
Consolas,'Lucida Console', monospace; font-size: 
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);"><br /></span></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class=""
style="font-family:Consolas,'Lucida Console', monospace; font-size: 12.8000001907349px; 
 
text-indent: -7px; background-color: rgb(240, 247, 255);"><br /></span></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><spanclass="" id="yui_3_16_0_1_1452135740077_60642" style="font-family: Consolas,

'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247, 255);">I
doknow that <span class="" id="yui_3_16_0_1_1452135740077_60687" style="font-size: 
 
12.8000001907349px;">PostgreSQL</span> does not support the public synonym.</span><span class=""
id="yui_3_16_0_1_1452135740077_60644"style="font-family: Consolas, 'Lucida Console', monospace; font-size: 
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);">  </span><span class="" id="yui_3_16_0_1_1452135740077_60646" style="font-family: Consolas, 'Lucida Console',
monospace;font-size: 
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);">Now, for a user schema (let's call it MASTER_USER), if I coded in the stored function/procedure like the
following, </span><spanclass="" id="yui_3_16_0_1_1452135740077_60794" style="font-family: Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);">withoutusing a public synonym to identify the </span><span class="" id="yui_3_16_0_1_1452135740077_60752"
style="font-family:Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px;">table name</span><span class=""
id="yui_3_16_0_1_1452135740077_60797"style="font-family: 
 
Consolas, 'Lucida Console', monospace; font-size: 12.8000001907349px; 
text-indent: -7px; background-color: rgb(240, 247, 255);">: </span></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><spanclass="" style="font-family: Consolas, 'Lucida Console', monospace;
font-size:
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);"><br /></span></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class=""
id="yui_3_16_0_1_1452135740077_60746"style="font-family: 
 
Consolas, 'Lucida Console', monospace; font-size: 12.8000001907349px; 
text-indent: -7px; background-color: rgb(240, 247, 255);">select user_name from </span><span class=""
id="yui_3_16_0_1_1452135740077_60836"style="font-family: Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);">user_info_table</span></div><divdir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class=""
style="font-family:Consolas, 'Lucida Console', monospace; font-size: 
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);"><br /></span></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class=""
id="yui_3_16_0_1_1452135740077_60750"style="font-family: 
 
Consolas, 'Lucida Console', monospace; font-size: 12.8000001907349px; 
text-indent: -7px; background-color: rgb(240, 247, 255);">then access it from the Java (web app) side</span><span
class=""id="yui_3_16_0_1_1452135740077_60648" style="font-family: Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);"> </span><spanclass="" id="yui_3_16_0_1_1452135740077_60650" style="font-family: Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);">viathe JDBC call to the database, will that work?</span><span class="" id="yui_3_16_0_1_1452135740077_60652"
style="font-family:Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);">  </span></div><divdir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class="" style="font-family: Consolas,
'LucidaConsole', monospace; font-size: 
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);"><br /></span></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class=""
id="yui_3_16_0_1_1452135740077_60654"style="font-family: Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);">OR, </span></div><divdir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class="" style="font-family:
Consolas,'Lucida Console', monospace; font-size: 
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);"><br /></span></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class=""
id="yui_3_16_0_1_1452135740077_60801"style="font-family:  
Consolas, 'Lucida Console', monospace; font-size: 12.8000001907349px; 
text-indent: -7px; background-color: rgb(240, 247, 255);">I must use the identifier inside the sql, such
as:</span></div><divdir="ltr" id="yui_3_16_0_1_1452135740077_60559"><span class="" style="font-family:Consolas, 'Lucida
Console',monospace; font-size: 12.8000001907349px; 
 
text-indent: -7px; background-color: rgb(240, 247, 255);"><br /></span></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><spanclass="" id="yui_3_16_0_1_1452135740077_60769" style="font-family: 
 
Consolas, 'Lucida Console', monospace; font-size: 12.8000001907349px; 
text-indent: -7px; background-color: rgb(240, 247, 255);"> select <span class="" id="yui_3_16_0_1_1452135740077_60875"
style="font-size:
 
12.8000001907349px;">user_name</span> from</span><span class="" id="yui_3_16_0_1_1452135740077_60656"
style="font-family:Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);">  </span><spanclass="" id="yui_3_16_0_1_1452135740077_60658" style="font-family: Consolas, 
 
'Lucida Console', monospace; font-size: 12.8000001907349px; text-indent:-7px; background-color: rgb(240, 247,
255);"><spanclass="" id="yui_3_16_0_1_1452135740077_60863" style="font-size: 
 
12.8000001907349px;">MASTER_USER</span>.<span class="" id="yui_3_16_0_1_1452135740077_60882" style="font-size: 
12.8000001907349px;">user_info_table</span>?</span><br /></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><spanclass="" style="font-family: Consolas, 'Lucida Console', monospace;
font-size:
 
12.8000001907349px; text-indent: -7px; background-color: rgb(240, 247, 
255);"><br /></span></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><br /></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559">Icome from the Oracle world, there I first create the public synonym for the
table,then in the stored procedure I just directly reference the table with no need to identify the table with a schema
name. Like to know how it work under the PostgreSQL.</div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"><br
/></div><divdir="ltr" id="yui_3_16_0_1_1452135740077_60559">  </div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"><brid="yui_3_16_0_1_1452135740077_60985" /></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"style="text-indent: 
 
-7px;"><font face="Consolas, Lucida Console, monospace"><span style="font-size: 
12.8000001907349px; background-color: rgb(240, 247, 255);">Thanks</span></font></div><div dir="ltr"
id="yui_3_16_0_1_1452135740077_60559"style="text-indent: -7px;"><font face="Consolas, Lucida 
 
Console, monospace"><span style="font-size: 12.8000001907349px; 
background-color: rgb(240, 247, 255);"><br /></span></font></div><div dir="ltr" id="yui_3_16_0_1_1452135740077_60559"
style="text-indent:-7px;"><font face="Consolas, Lucida 
 
Console, monospace"><span style="font-size: 12.8000001907349px; 
background-color: rgb(240, 247, 255);">Eugene</span></font></div></div></div></blockquote><br />