Thread: table synonyms

Hi,

I would like to know if PostgreSQL has a similar database object to the
Oracle synonym.

Thanks a lot.

Jayme Jeffman Filho
GSEE-PUCRS
+55 (51) 9112 3422

I don't remember off the top of my head exactly how synonyms worked, but
I'm pretty sure PostgreSQL doesn't directly support them. You might be
able to emulate them with rules, though.

On Mon, May 16, 2005 at 08:35:34AM -0300, Jayme Jeffman Filho wrote:
> Hi,
>
> I would like to know if PostgreSQL has a similar database object to the
> Oracle synonym.
>
> Thanks a lot.
>
> Jayme Jeffman Filho
> GSEE-PUCRS
> +55 (51) 9112 3422
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

I have searched for the word synonym through the whole
PostrgeSQL 7.42 pdf documentation and all the ocurrences
are from functions redefinitions, and another PostreSQL
user has answered me that it does not support tables
synonyms, so I am with a big problem : How can I write
queries to suport different tables owners ?

Let me explain : I run the same application at different
database servers, from different enterprises, and their
DBA's can choose the name of the owner of the tables I
should query to. How to manage this ? Can I use a parameter
to define the table owner ? A macro ? Is there any
solution?

Thanks a lot.

Jayme.

----- Original Message -----
From: "Jim C. Nasby"
To: Jayme Jeffman Filho
Sent: 20-May-2005 13:31:49 -0300
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] table synonyms
I don't remember off the top of my head exactly how synonyms worked, but
I'm pretty sure PostgreSQL doesn't directly support them. You might be
able to emulate them with rules, though.

I suspect you're mixing users and schemas, but it's been too long since
I've used Oracle, so I'm not sure. Can you provide a more concrete
example? FWIW, I suspect this is a non-issue with postgresql, since the
only hierarchy of objects is schemas, and you can handle that with
search_path.

On Fri, May 20, 2005 at 03:01:34PM -0300, jjeffman@cpovo.net wrote:
> I have searched for the word synonym through the whole
> PostrgeSQL 7.42 pdf documentation and all the ocurrences
> are from functions redefinitions, and another PostreSQL
> user has answered me that it does not support tables
> synonyms, so I am with a big problem : How can I write
> queries to suport different tables owners ?
>
> Let me explain : I run the same application at different
> database servers, from different enterprises, and their
> DBA's can choose the name of the owner of the tables I
> should query to. How to manage this ? Can I use a parameter
> to define the table owner ? A macro ? Is there any
> solution?
>
> Thanks a lot.
>
> Jayme.
>
> ----- Original Message -----
> From: "Jim C. Nasby"
> To: Jayme Jeffman Filho
> Sent: 20-May-2005 13:31:49 -0300
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] table synonyms
> I don't remember off the top of my head exactly how synonyms worked, but
> I'm pretty sure PostgreSQL doesn't directly support them. You might be
> able to emulate them with rules, though.
>
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Sun, May 22, 2005 at 03:28:22PM -0300, jjeffman@cpovo.net wrote:
> Of course! Maybe I am mixing users and schemas, because in Oracle they
> are the same, the schema has the name of the user which is the owner of
> the database tables and objects.
>
> So the problem can be described as follows :
>
> 1. Let be Ent01 an enterprise, and Ent02 a different one.
> 2. At Ent01 the database schema has the name "SCH01" and at Ent02 the
> database schema has the name "FOO" .
> 3. The same application must run in booth enterprises, and all the
> database queries and table names are the same, just the schemas has
> different names.
> 4. The application can run using a database user other then the tables
> owner, so the queries must be written using the coplete format
> (schema.table.column) .
>
> Using Oracle I can set up synonyms for the tables and by pass the format
> above, or I can use a macro substitution (ODAC components) to use the
> correct schema name, setting it at runtime.

In PostgreSQL, you can get roughly the same behavior using search_path.
http://lnk.nu/postgresql.org/2r2.html

> By the way what "FWIW" stands for ?

For What It's Worth.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Thank you very much for your answer.

What happens if there are more than one table with the same name in the "search_path" ?


Jayme Jeffman Filho
GSEE-PUCRS
+55 51 91123422

Of course! Maybe I am mixing users and schemas, because in Oracle they are the same, the schema has the name of the user which is the owner of the database tables and objects.

So the problem can be described as follows :

1. Let be Ent01 an enterprise, and Ent02 a different one.
2. At Ent01 the database schema has the name "SCH01" and at Ent02 the database schema has the name "FOO" .
3. The same application must run in booth enterprises, and all the database queries and table names are the same, just the schemas has different names.
4. The application can run using a database user other then the tables owner, so the queries must be written using the coplete format (schema.table.column) .

Using Oracle I can set up synonyms for the tables and by pass the format above, or I can use a macro substitution (ODAC components) to use the correct schema name, setting it at runtime.

As you told me PostgreSQL does not has table synonyms, I would like to write a query like "SELECT alias.column FROM &schema.table AS alias", and set up the &schema value at runtime. This way the query could be ran in every schema which has the table.

That is the problem how to do this in PostgreSQL ? I am planing to use Zeos database components.

By the way what "FWIW" stands for ?

Thank you very much.

----- Original Message -----
From: "Jim C. Nasby"
To: jjeffman@cpovo.net
Sent: 21-May-2005 15:15:49 -0300
Subject: Re: [GENERAL] table synonyms
I suspect you're mixing users and schemas, but it's been too long since
I've used Oracle, so I'm not sure. Can you provide a more concrete
example? FWIW, I suspect this is a non-issue with postgresql, since the
only hierarchy of objects is schemas, and you can handle that with
search_path.

On Mon, May 23, 2005 at 09:07:08AM -0300, jjeffman@cpovo.net wrote:
> Thank you very much for your answer.
>
> What happens if there are more than one table with the same name in the
> "search_path" ?

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   <kleptog@svana.org>   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.

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.

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?

> > 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?

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

Am Dienstag, den 24.05.2005, 10:37 -0500 schrieb Mike Nolan:
> > > 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?
>
> 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.

It will be a very pleasant idea. Although I am
an application developer I don't know if I have
enough knowledge to do that. Do you know how could
I help the PostgreSQL development ?

Another feature I missed is the "returning" clause
of the Oracle "INSERT" SQL command, which allow the
user to retrieve the "serial" value after an insert
command, which works even in a concurrent network
environment.

Thanks a lot.

--
Jayme Jeffman Filho
GSEE - PUCRS
+55 51 91123422

----- Original Message -----
From: Mike Nolan
To: tino@wildenhain.de (Tino Wildenhain)
Sent: 24-May-2005 12:49:39 -0300
CC: jjeffman@cpovo.net, pgsql-general@postgresql.org (Postgresql-General)
Subject: Re: [GENERAL] table synonyms
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

On Tue, May 24, 2005 at 13:49:40 -0300,
  jjeffman@cpovo.net wrote:
>
> Another feature I missed is the "returning" clause
> of the Oracle "INSERT" SQL command, which allow the
> user to retrieve the "serial" value after an insert
> command, which works even in a concurrent network
> environment.

While it might be nice to have a returning clause (and there has been
discussion of that in the past), you can do what want.
The currval function returns the last value assigned by nextval in
the current session and is safe from conflicts with concurrent operations.

Am Dienstag, den 24.05.2005, 13:49 -0300 schrieb jjeffman@cpovo.net:
> It will be a very pleasant idea. Although I am
> an application developer I don't know if I have
> enough knowledge to do that. Do you know how could
> I help the PostgreSQL development ?
>
> Another feature I missed is the "returning" clause
> of the Oracle "INSERT" SQL command, which allow the
> user to retrieve the "serial" value after an insert
> command, which works even in a concurrent network
> environment.

INSERT INTO table (...) values (...);
SELECT currval('table_id_seq');

See documentation for sequences.

On Tue, 2005-05-24 at 19:48 +0200, Tino Wildenhain wrote:
> Am Dienstag, den 24.05.2005, 13:49 -0300 schrieb jjeffman@cpovo.net:
> > It will be a very pleasant idea. Although I am
> > an application developer I don't know if I have
> > enough knowledge to do that. Do you know how could
> > I help the PostgreSQL development ?
> >
> > Another feature I missed is the "returning" clause
> > of the Oracle "INSERT" SQL command, which allow the
> > user to retrieve the "serial" value after an insert
> > command, which works even in a concurrent network
> > environment.
>
> INSERT INTO table (...) values (...);
> SELECT currval('table_id_seq');
>
> See documentation for sequences.
>

At times when I have been using a serial number of one table as a link
for another.  I have set the 'id' field to a type bigint then used the
code below

SELECT nexval('table_id_seq');
number = result;
INSERT INTO table (id,...) values (number,...);

Then used the value in 'number' for other queries.  If two users do the
same query at the same time they both get different values and each wont
get confused as to who used what value.

>
>
> ---------------------------(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