Thread: CREATE SYNONYM in PostgreSQL
Hello, We are converting the Oracle's CREATE SYNONYM statement into PostgreSQL. I think to replace the SYNONYM we use search_path in PostgreSQL and the same thing is explained in the below post also. http://postgresql.1045698.n5.nabble.com/Synonyms-in-PostgreSQL-9-2-4-td5757986.html Is there any way to automate the oracle's CREATE SYNONYM in PostgreSQL or can we use hook like post_parse_analyze_hook to implement this? Thought? ----- Regards, Vinayak, -- View this message in context: http://postgresql.1045698.n5.nabble.com/CREATE-SYNONYM-in-PostgreSQL-tp5818446.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Vinayak wrote: > We are converting the Oracle's CREATE SYNONYM statement into PostgreSQL. > I think to replace the SYNONYM we use search_path in PostgreSQL and the same > thing is explained in the below post also. > http://postgresql.1045698.n5.nabble.com/Synonyms-in-PostgreSQL-9-2-4-td5757986.html > > Is there any way to automate the oracle's CREATE SYNONYM in PostgreSQL or > can we use hook like post_parse_analyze_hook to implement this? > Thought? If search_path is too coarse (affects all objects in a schema), I recommend that you use views to replace Oracle's synonyms. Yours, Laurenz Albe
Vinayak <vinpokale@gmail.com> writes: > Hello, > We are converting the Oracle's CREATE SYNONYM statement into PostgreSQL. > I think to replace the SYNONYM we use search_path in PostgreSQL and the same > thing is explained in the below post also. > http://postgresql.1045698.n5.nabble.com/Synonyms-in-PostgreSQL-9-2-4-td5757986.html > > Is there any way to automate the oracle's CREATE SYNONYM in PostgreSQL or > can we use hook like post_parse_analyze_hook to implement this? > Thought? The fine people at EnterpriseDB offer "Postgres Plus Advanced SErver" which implements this and many other Oracle look allike features. HTH > > > > > ----- > Regards, > Vinayak, > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/CREATE-SYNONYM-in-PostgreSQL-tp5818446.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On 09/10/2014 06:50 AM, Vinayak wrote: > > Hello, > We are converting the Oracle's CREATE SYNONYM statement into PostgreSQL. > I think to replace the SYNONYM we use search_path in PostgreSQL and the same > thing is explained in the below post also. > http://postgresql.1045698.n5.nabble.com/Synonyms-in-PostgreSQL-9-2-4-td5757986.html > > Is there any way to automate the oracle's CREATE SYNONYM in PostgreSQL or > can we use hook like post_parse_analyze_hook to implement this? > Thought? If you are planning on submitting this to .Org you should check the archives for previous discussions on this feature. jD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, @cmdpromptinc "If we send our children to Caesar for their education, we should not be surprised when they come back as Romans."
Vinayak,
* Vinayak (vinpokale@gmail.com) wrote:
> We are converting the Oracle's CREATE SYNONYM statement into PostgreSQL.
Ah, I remember having to deal with exactly that issue when migrating
from Oracle.
> I think to replace the SYNONYM we use search_path in PostgreSQL and the same
> thing is explained in the below post also.
There are pros and cons to this approach. In general, I'd recommend
using a simple view instead of trying to use the search_path- for
example, prepared queries will look up the OID based on the current
search_path. If you prepare a query, then change your search_path, and
run that prepared query, it's going to use the table which was resolved
using the search_path when the query was initially planned.
> Is there any way to automate the oracle's CREATE SYNONYM in PostgreSQL or
> can we use hook like post_parse_analyze_hook to implement this?
You could try but that doesn't seem likely to work out too well..
In general, this has been discussed a number of times in the past (I
brought it up when I ran into the issue originally too..) and I continue
to feel that it'd be good for us to have, but the argument is that
anything done to support synonyms would necessairly slow down name
resolution and could complicate other things. Still, I'm hopeful that
someone with a good use-case for synonyms will get tired of having to
use such hacks and will have time (or funds) to put towards figuring out
how to add them to PG.
Thanks,
Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes:
> In general, this has been discussed a number of times in the past (I
> brought it up when I ran into the issue originally too..) and I continue
> to feel that it'd be good for us to have, but the argument is that
> anything done to support synonyms would necessairly slow down name
> resolution and could complicate other things. Still, I'm hopeful that
> someone with a good use-case for synonyms will get tired of having to
> use such hacks and will have time (or funds) to put towards figuring out
> how to add them to PG.
It's been a long time, but I seem to remember that one of the main
use-cases for Oracle synonyms was cross-database links; so at the
time this was last discussed, we just threw up our hands and said
"we can't support that". No amount of search-path game playing will
cover that case, and not plain views either. Today, however, I wonder
whether you can't just use postgres_fdw. Admittedly it might have some
performance issues...
regards, tom lane
Hello, >There are pros and cons to this approach. In general, I'd recommend >using a simple view instead of trying to use the search_path- for >example, prepared queries will look up the OID based on the current >search_path. If you prepare a query, then change your search_path, and >run that prepared query, it's going to use the table which was resolved >using the search_path when the query was initially planned. I agreed to use view instead of search_path. >> Is there any way to automate the oracle's CREATE SYNONYM in PostgreSQL or >> can we use hook like post_parse_analyze_hook to implement this? >You could try but that doesn't seem likely to work out too well.. I want to convert Oracle CREATE SYNONYM statement into PostgreSQL CREATE VIEW statement in my own extension not in the PostgreSQL core. Is it possible to parse the CREATE SYNONYM statement and convert into CREATE VIEW statement using post_parse_analyze_hook? or is there any other idea to automate this process? ----- Regards, Vinayak, -- View this message in context: http://postgresql.1045698.n5.nabble.com/CREATE-SYNONYM-in-PostgreSQL-tp5818446p5818755.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Vinayak <vinpokale@gmail.com> writes:
> Is it possible to parse the CREATE SYNONYM statement and convert into CREATE
> VIEW statement using post_parse_analyze_hook? or is there any other idea to
> automate this process?
You will not be able to do it without modifying the grammar. SYNONYM
isn't even a keyword in stock PG.
regards, tom lane
Thank you for replay. >You will not be able to do it without modifying the grammar. SYNONYM >isn't even a keyword in stock PG. If I understood correctly since SYNONYM is not the part of the grammar, the parser throw a syntax error before reaching to hook. ----- Regards, Vinayak, -- View this message in context: http://postgresql.1045698.n5.nabble.com/CREATE-SYNONYM-in-PostgreSQL-tp5818446p5818779.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.