Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aHH9Y-0005Xq-PO for pgsql-sql@arkaria.postgresql.org; Thu, 07 Jan 2016 20:27:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aHH9Y-0000vx-C0 for pgsql-sql@arkaria.postgresql.org; Thu, 07 Jan 2016 20:27:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aHH9X-0000vr-Vu for pgsql-sql@postgresql.org; Thu, 07 Jan 2016 20:27:24 +0000 Received: from mout.perfora.net ([74.208.4.196]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84) (envelope-from ) id 1aHH9U-00056h-0v for pgsql-sql@postgresql.org; Thu, 07 Jan 2016 20:27:22 +0000 Received: from [192.168.1.122] ([68.98.130.52]) by mrelay.perfora.net (mreueus001) with ESMTPSA (Nemesis) id 0MEm44-1aRlXN0EKg-00G8K8; Thu, 07 Jan 2016 21:27:15 +0100 Message-ID: <568ECA21.20702@sqlexec.com> Date: Thu, 07 Jan 2016 15:27:13 -0500 From: "michael@sqlexec.com" User-Agent: Postbox 4.0.8 (Windows/20151105) MIME-Version: 1.0 To: Eugene Yin CC: "pgsql-sql@postgresql.org" Subject: Re: public synonym References: <358282890.1765953.1452197860946.JavaMail.yahoo.ref@mail.yahoo.com> <358282890.1765953.1452197860946.JavaMail.yahoo@mail.yahoo.com> In-Reply-To: <358282890.1765953.1452197860946.JavaMail.yahoo@mail.yahoo.com> Content-Type: multipart/alternative; boundary="------------010909010501020802070704" X-Provags-ID: V03:K0:42SoZhYElUGdEa2qD7JFduaCqwuw9tXKW9iNdgycCCyygDMX2R3 Hz/aruSycNCcE84lFpSSBqIiNp7sMPsbKoPle0+VoJ67AwJYfGsvKSouWQrnWm/+/DUWRd7 bjQ+9BTQh62EXY5Hd9Vqb/a6v5krW1/nI5p+CFcnRx2IqgZ121+4tJroLs0UfHAg1Um19uv N/AU0H7sAoILu3g51rD4Q== X-UI-Out-Filterresults: notjunk:1;V01:K0:sba7gK/SZNg=:XChxM2EqK2ay07SyCIrBs+ vYrY8e8pUzbgnonLUxr7AacHLFcEaPG33LNhnT2o6EmJ+qURBEI3+8++olSpd+gbo0vs9mY+f U43elVuNgvaOXEReBz0klQxP+MQfErcYDeDwIvkTWRJtLM4SUyHjkrg6aDJZQWlBV3sA65Ul4 hh/lb3By3UtX0PpEbKuS3KzdB00eIT1YECERVHYwD/KsmavqDg8ekZfMMdYySLvuzLoOg57Yj WxS3ob0wEE9/sSzwU9dfNillhu0D1RPhMG/sNCdCIgbTgG7OFngR3slaFs46ownFUfZ15d8tI c+MByPGdo5+JXDAjbZW7pDa9rn+xYdqVRenHrP6sEw60LdA8/NVvcY9+0NvEcKw3oX1i24zpV OigPBReZ3HINzt2MG3mX1qBkUxh1y32eWsyHMotNLEPTQxJLeyuDqo9/BuW4y7IdnlA/qjKlr RI/8r+INmt3TCy01WGaA3GmmINS3SgI4s1oxeTEa5KAaw3ePcCKFwhBYfcHbY9U9F8eYSXrAi tiVa2zVOe92QkS7pAadN9oCYUYtuQRApYI3KxVrZhS5qaGbTDEQdTOYT3F/Xr7sFi5oXV9OfR rfubtrRu1uNpnvFwrQJcW6wvkLOVgOU5fJFvHAEIZImndfPK9yTzjFCQUqYJOlV31s+z6QnyS /JNubZa4B8HD4h/F+SvTkqKGOIjHEQMhpa3wjwacYnuhkMcTU2lWl5umUsfsZklAwV+gwcrmc b0u4tppKtPKSuDrN X-Pg-Spam-Score: -0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------010909010501020802070704 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit You can infer the context by first setting the search_path variable. You can set it initially in your connection or do it for a database context or even a role context SET search_path = MASTER_USER, public, pg_catalog; ALTER DATABASE whatever SET search_path = MASTER_USER, public, pg_catalog; ALTER ROLE whoever SET search_path = MASTER_USER, public, pg_catalog; Then you can continue to let the tables be non-qualified. bye bye synonyms! Regards Michael > Eugene Yin > Thursday, January 7, 2016 3:17 PM > PostgreSQL ver 9.4.5. Linux OS. > Application: Web Based > > Platform: > App Server (java) --> jdbc call --> Database Server (PostgreSQL) > > > I do know that PostgreSQL does not support the public synonym.Now, for > a user schema (let's call it MASTER_USER), if I coded in the stored > function/procedure like the following, without using a public synonym > to identify the table name: > > select user_name from user_info_table > > then access it from the Java (web app) sidevia the JDBC call to the > database, will that work? > > OR, > > I must use the identifier inside the sql, such as: > > select user_name fromMASTER_USER.user_info_table? > > > I come 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. > > > Thanks > > Eugene --------------010909010501020802070704 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit You can infer the context by first setting the search_path variable.  You can set it initially in your connection or do it for a database context or even a role context
SET search_path = MASTER_USER, public, pg_catalog;
ALTER DATABASE whatever  SET search_path = MASTER_USER, public, pg_catalog;
ALTER ROLE whoever SET search_path = MASTER_USER, public, pg_catalog;

Then you can continue to let the tables be non-qualified.   bye bye synonyms!

Regards
Michael
Thursday, January 7, 2016 3:17 PM
PostgreSQL ver 9.4.5.  Linux OS.
Application:  Web Based

Platform:  
    App Server (java) --> jdbc call --> Database Server (PostgreSQL)


I do know that PostgreSQL does not support the public synonym.  Now, for a user schema (let's call it MASTER_USER), if I coded in the stored function/procedure like the following, without using a public synonym to identify the table name: 

select user_name from user_info_table

then access it from the Java (web app) side via the JDBC call to the database, will that work?  

OR, 

I must use the identifier inside the sql, such as:

 select user_name from  MASTER_USER.user_info_table?


I come 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.

  

Thanks

Eugene

--------------010909010501020802070704--