Обсуждение: Returning oid or primary key
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
(If this is the second time you've seen this message, sorry about that...
it didn't seem to go through the first time I sent it.)
Folks-
I've got an idea on a possible extension to the jdbc driver. I want
to submit a patch, but only if others think it makes sense. I want to
add a 'clause' that would slightly change the return value of the
executeUpdate(String) method in the Statment class. The goal is to
allow the return of the oid instead of the number of rows affected in
certain cases. For example:
insert into tablename(name) values('sample') returning oid
as the string would cause the returning value of the executeUpdate()
method to be the oid of inserted row (if successful) instead of the
count of updated rows.
I like the idea since I can now use standard pooling managers (like
PoolMan) and have access to the oid without having access to the
postgresql Statement class. Of course, the problem is that I'm now
'overloading' the returning value of the method, which is ugly. Also,
'faking' SQL syntax in the jdbc driver that doesn't exist in the
postgresql backend may also be problematic. Since callable statement
doesn't support returned values yet, I figure this would help.
What are people's thoughts about this?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: Public key at http://www.keyserver.net
iD8DBQE68X3PiysnOdCML0URAlrxAJ90YtoR1lPGjeDGXJulXcJtS5aslACaArqq
/oGoNBmIRVx8gOn+PRy+rLY=
=Uiae
-----END PGP SIGNATURE-----
--
Virtually,
Ned Wolpert <ned.wolpert@knowledgenet.com>
D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> From: Bruce Momjian <pgman@candle.pha.pa.us>
> Date: Thu, 3 May 2001 13:29:55 -0400 (EDT)
> > insert into tablename(name) values('sample') returning oid
> >
> > as the string would cause the returning value of the executeUpdate()
> > method to be the oid of inserted row (if successful) instead of the
> > count of updated rows.
> >
> So you really want the OID from the update? We want to add RETURNING to
> the standard SQL capabilities of the backend.
Well... I want the oid from the update, but also the ability to use
Callable Statement to get the primary key as well.
The issue is the ability to find the generated index for the row
easilly with jdbc, without having to figure out if I'm using the
org.postgresql.Statement class. (Which with some pool managers, isn't
directly available.) I can use the OID to find the row just inserted,
and then lookup the generated index. If the callable statement could
return a value, then it's easy. But the class isn't really
implemented for that in the current postgresql jdbc driver. I figure
that until RETURNING is added to the backend, and callable statement
works, this would be an 'ok' workaround. Not a great workaround,
since I don't like 'overloading' a return value, especially in Java.
However, _if_ RETURNING is going to be in the standard SQL for the
next release, _and_ there are plans to update the jdbc driver to take
advantage of that in the callable statement, as well, then it makes
more sense _not_ to add my patch that implements the 'overloading' of
the return value in the jdbc driver.
I guess the issue is the ability to return the primary key from an
inserted row, for cases that it's auto-generated, such as using the
serial type.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: Public key at http://www.keyserver.net
iD8DBQE68ZgJiysnOdCML0URAkyZAJ9+dW+SSsYf0E0i2IthciFbj/tojACeIHyF
47YW7+yMta3wFs1loosfEfw=
=j1lP
-----END PGP SIGNATURE-----
--
Virtually,
Ned Wolpert <ned.wolpert@knowledgenet.com>
D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45
-- Start of PGP signed section.
> (If this is the second time you've seen this message, sorry about that...
> it didn't seem to go through the first time I sent it.)
>
>
> Folks-
>
> I've got an idea on a possible extension to the jdbc driver. I want
> to submit a patch, but only if others think it makes sense. I want to
> add a 'clause' that would slightly change the return value of the
> executeUpdate(String) method in the Statment class. The goal is to
> allow the return of the oid instead of the number of rows affected in
> certain cases. For example:
>
> insert into tablename(name) values('sample') returning oid
>
> as the string would cause the returning value of the executeUpdate()
> method to be the oid of inserted row (if successful) instead of the
> count of updated rows.
>
> I like the idea since I can now use standard pooling managers (like
> PoolMan) and have access to the oid without having access to the
> postgresql Statement class. Of course, the problem is that I'm now
> 'overloading' the returning value of the method, which is ugly. Also,
> 'faking' SQL syntax in the jdbc driver that doesn't exist in the
> postgresql backend may also be problematic. Since callable statement
> doesn't support returned values yet, I figure this would help.
So you really want the OID from the update? We want to add RETURNING to
the standard SQL capabilities of the backend.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> However, _if_ RETURNING is going to be in the standard SQL for the > next release, _and_ there are plans to update the jdbc driver to take > advantage of that in the callable statement, as well, then it makes > more sense _not_ to add my patch that implements the 'overloading' of > the return value in the jdbc driver. > > I guess the issue is the ability to return the primary key from an > inserted row, for cases that it's auto-generated, such as using the > serial type. Certainly the portable solution is to get RETURNING into the backend so you can decide what you want to return. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > From: Bruce Momjian <pgman@candle.pha.pa.us> > Date: Thu, 3 May 2001 13:42:23 -0400 (EDT) > Certainly the portable solution is to get RETURNING into the backend so > you can decide what you want to return. I agree. Is that currently planned for the next release? -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: Public key at http://www.keyserver.net iD8DBQE68ZjfiysnOdCML0URAnAkAJ4h2r21+uvdZzw14Prmy8SgvqJs2QCeMMN8 YpwRmYJkfJdzZnkpqTLfI3Q= =PqCG -----END PGP SIGNATURE----- -- Virtually, Ned Wolpert <ned.wolpert@knowledgenet.com> D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45
-- Start of PGP signed section. > > From: Bruce Momjian <pgman@candle.pha.pa.us> > > Date: Thu, 3 May 2001 13:42:23 -0400 (EDT) > > > Certainly the portable solution is to get RETURNING into the backend so > > you can decide what you want to return. > > I agree. Is that currently planned for the next release? I don't know. That is the problem. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026