PostgreSQL Discussion - Re: table synonyms

Author

Re: table synonyms

jjeffman@cpovo.net

2005-05-24, 3:23 am

So the search_path can not substitute the Oracle synonyms function which
in a single way allow us to write generic queries for an application no
matter the schema which is being used.

I can not find a solution on this matter in PostgreSQL.

I hope anybody can help me on this subject. The problem is not to find a
substitute for the Oracle synonyms, but a way to write queries which,
reliably, can be used no matter the schema which owns the tables.

Jayme

----- Original Message -----
From: Martijn van Oosterhout
To: jjeffman@cpovo.net
Sent: 23-May-2005 12:53:30 -0300
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] table synonyms
On Mon, May 23, 2005 at 09:07:08AM -0300,

It takes the first one.

There is no explicit relationship between users and schemas, however
many installations have the search_path default to "$user, public"
which is magically substituted on connection. You are ofcourse free to
alter search_path whenever you want. Any table can always be referred
to by its full name...

Hope this helps,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is
a
> tool for doing 5% of the work and then sitting around waiting for
someone
> else to do the other 95% so you can sue them.

Tino Wildenhain

2005-05-24, 3:23 am

Am Montag, den 23.05.2005, 23:21 -0300 schrieb jjeffman@cpovo.net:
> So the search_path can not substitute the Oracle synonyms function
> which in a single way allow us to write generic queries for an
> application no matter the schema which is being used.
>
> I can not find a solution on this matter in PostgreSQL.
>
> I hope anybody can help me on this subject. The problem is not to find
> a substitute for the Oracle synonyms, but a way to write queries
> which, reliably, can be used no matter the schema which owns the
> tables.

Maybe you use views?

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Mike Nolan

2005-05-24, 11:23 am

> > I hope anybody can help me on this subject. The problem is not to find
>
> Maybe you use views?

Unless it changed in 8, you can't insert into or update a view.

I don't know if rules will do the trick or not, to be honest I haven't
figured out what they can and cannot do.

As someone who used to use synonyms at the user/schema level in Oracle
as a way to restrict access to a subset tables based on user-specific
criteria (eg, restricting salesman 'X' to only his accounts in the customer
master table), yes, synonyms would be nice.

But if you really want them, become part of the development effort.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tino Wildenhain

2005-05-24, 1:23 pm

Am Dienstag, den 24.05.2005, 10:37 -0500 schrieb Mike Nolan:
>
> Unless it changed in 8, you can't insert into or update a view.
>
> I don't know if rules will do the trick or not, to be honest I haven't
> figured out what they can and cannot do.

Yes, since views are basically constructed via rules, you can extend
them with rules for update, insert etc. as well.

> As someone who used to use synonyms at the user/schema level in Oracle
> as a way to restrict access to a subset tables based on user-specific
> criteria (eg, restricting salesman 'X' to only his accounts in the customer
> master table), yes, synonyms would be nice.

Views I'd say. Or you do it via SRF (set returning functions)
to isolate access.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sponsored Links