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.

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

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

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/

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

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.