Re: CREATE SYNONYM in PostgreSQL

Vinayak,

* Vinayak (vinpokale(at)gmail(dot)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