On Thu, 9 Mar 2006, Jonah H. Harris wrote:

> 2) For my comprehension, what's the difference between a SYNONYM and a
>> single-object (possibly updatable) view?
>
> Not a whole lot actually. If we had updateable views, I'd suggest that
> people change their create synonym syntax to create view.

One key difference would be that synonyms track schema updates, like
adding a column, to the referenced object that a view would not.

Kris Jurka


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Kris Jurka <books@ejurka.com> writes:
> One key difference would be that synonyms track schema updates, like
> adding a column, to the referenced object that a view would not.

That raises a fairly interesting point, actually. What would you expect
to happen here:

CREATE TABLE foo ...;
CREATE SYNONYM bar FOR foo;
CREATE VIEW v AS SELECT * FROM bar;
DROP SYNONYM bar;

With the implementations being proposed, v would effectively be stored
as "SELECT * FROM foo" and thus would be unaffected by the DROP SYNONYM.
Is that what people will expect? Is it what happens in Oracle?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On fös, 2006-03-10 at 16:51 -0500, Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
> > One key difference would be that synonyms track schema updates, like
> > adding a column, to the referenced object that a view would not.
>
> That raises a fairly interesting point, actually. What would you expect
> to happen here:
>
> CREATE TABLE foo ...;
> CREATE SYNONYM bar FOR foo;
> CREATE VIEW v AS SELECT * FROM bar;
> DROP SYNONYM bar;
>
> With the implementations being proposed, v would effectively be stored
> as "SELECT * FROM foo" and thus would be unaffected by the DROP SYNONYM.
> Is that what people will expect? Is it what happens in Oracle?


At least on Oracle8, you could create a synonym on a
non-existing table, so if table FOO does not exist:

CREATE SYNONYM BAR FOR FOO; -- no error
SELECT * FROM BAR; -- error "synonym translation is no longuer valid"
CREATE TABLE FOO (a varchar2(10));
INSERT INTO FOO VALUES ('a');
SELECT * FROM BAR; -- no error
CREATE VIEW X AS SELECT * FROM BAR;
SELECT * FROM X; -- no error
DROP SYNONYM X; -- no error
SELECT * FROM BAR; -- error

gnari



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings