PostgreSQL Discussion - Re: table synonyms

Author

Re: table synonyms

jjeffman@cpovo.net

2005-05-24, 1:23 pm

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

Bruno Wolff III

2005-05-24, 1:23 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Tino Wildenhain

2005-05-24, 1:23 pm

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_se
q');

See documentation for sequences.

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

Neil Dugan

2005-05-28, 8:23 pm

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:
>
> INSERT INTO table (...) values (...);
> SELECT currval('table_id_se
q');
>
> 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

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

Sponsored Links