CREATE SYNONYM in PostgreSQL

[Vinayak]

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.

[Albe Laurenz]

Post by Vinayak
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

[Jerry Sievers]

Post by Vinayak
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

Post by Vinayak
-----
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: ***@comcast.net
p: 312.241.7800

[Joshua D. Drake]

Post by Vinayak
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."

[Stephen Frost]

Vinayak,

Post by Vinayak
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.

Post by Vinayak
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.

Post by Vinayak
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

[Tom Lane]

Post by Stephen Frost
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

[Vinayak]

Hello,

Post by Stephen Frost
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.

Post by Stephen Frost

Post by Vinayak
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.

[Tom Lane]

Post by Vinayak
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

[Vinayak]

Thank you for replay.

Post by Tom Lane
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.