Re: Synthesize support for Statement.getGeneratedKeys()?

On 21-Jan-07, at 7:52 PM, Ken Johanson wrote:

>
>>>>> 4) If 3b is required, then besides incrementing (by one)
>>>>> sequences, any suggestions on how to correctly synthesize other
>>>>> increment values? Other key types (OIDs, etc?)
>>>> Are you suggesting here that you are planning on incrementing
>>>> the sequences by 1 ? Why not just let the insert occur and get
>>>> the currval of the sequence ?
>>>
>>> I'm thinking what you're thinking; get the current value; however
>>> the increment I mention is just in case I cant get more than one
>>> curval... I don't know, can I get 3 values from RETURN if I
>>> insert three VALUEs in one query??
>> hmmm good question, one which I doubt the Sun people thought
>> about. I wouldn't bother trying to return any more than the last one.
>>>
>
>
> Well, unless server generated keys can only be numeric (increment
> by one, i.e predictable) (which is the only kind I've used), then I
> think it's essential that we somehow can get each of the generated
> keys.
>
> As for Sun/others, I do know that this feature works well in other
> dbms's - every one I've used supports it, and with multiple rows
> inserted. But I've only ever used numeric server generated keys so
> I don't know if other types are supported on those DBs.
OK, I should look at the spec before making statements above. This is
possible in postgres, you can get whatever values were auto generated
by the insert
>
> As an aside, how do PG jdbc users get the server generated keys? Or
> does everyone use some kind of UUID system (which I think is
> generally regarded as detrimental to indexes/memory under high load
> and large DB sizes - compared to int/bigint)? Or do PG users using
> some standard or server-specific (RETURNING) SQL clause?

either create the key ahead of time select nextval('sequence') and
insert it explicitly, or insert the row and  then  select currval
('sequence')

Dave
>
> ken
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>