Thread: CREATE SYNONYM in PostgreSQL

CREATE SYNONYM in PostgreSQL

From
Vinayak
Date:
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.


Re: CREATE SYNONYM in PostgreSQL

From
Albe Laurenz
Date:
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

Re: CREATE SYNONYM in PostgreSQL

From
Jerry Sievers
Date:
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


Re: CREATE SYNONYM in PostgreSQL

From
"Joshua D. Drake"
Date:
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."


Re: CREATE SYNONYM in PostgreSQL

From
Stephen Frost
Date:
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

Re: CREATE SYNONYM in PostgreSQL

From
Tom Lane
Date:
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


Re: CREATE SYNONYM in PostgreSQL

From
Vinayak
Date:
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.


Re: CREATE SYNONYM in PostgreSQL

From
Tom Lane
Date:
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


Re: CREATE SYNONYM in PostgreSQL

From
Vinayak
Date:
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.