Re: [GENERAL] Can I getting a unique ID from a select

2007-03-06 Thread Timasmith 
On Mar 5, 3:35 am, [EMAIL PROTECTED] (Bruno Wolff III) wrote:
 On Sat, Mar 03, 2007 at 16:46:45 -0800,
  Timasmith[EMAIL PROTECTED] wrote:

  On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote:
   On Thu, Mar 01, 2007 at 06:16:02 -0800,
Timasmith[EMAIL PROTECTED] wrote:

create view myview as
select rownum, t1.field, t2.field
from tableOne t1, tableTwo t2
where t1.key = t2.fkey

  Never heard of a 'join key' but that sounds very promising.  How do I
  select it?

 The join key would be t1.key or t2.fkey from your example. However there
 may be multiple rows returned with the same value depending on what you
 are joining. If that is the case you, should be able to use the primary
 keys of the underlying tables to make a new candidate key for the joined
 rows.

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

Multiple rows with the same key renders Hibernate useless as it caches
the 'row object' and then returns the first row every time for that
object.

I think the sequence will work though, in reflection I guess it would
as fast as pulling another field, and with the numbers would be a very
long time before getting duplicates - even if you had thousands of
users, returning 100s of rows every few minutes (I think...).


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

Re: [GENERAL] Can I getting a unique ID from a select

2007-03-06 Thread Bruno Wolff III 
On Mon, Mar 05, 2007 at 17:07:25 -0800,
  Timasmith [EMAIL PROTECTED] wrote:
 
 create view myview as
 select rownum, t1.field, t2.field
 from tableOne t1, tableTwo t2
 where t1.key = t2.fkey
 
 Multiple rows with the same key renders Hibernate useless as it caches
 the 'row object' and then returns the first row every time for that
 object.
 
 I think the sequence will work though, in reflection I guess it would
 as fast as pulling another field, and with the numbers would be a very
 long time before getting duplicates - even if you had thousands of
 users, returning 100s of rows every few minutes (I think...).

Based on the naming (t1.key vs t2.fkey) it looks like you may have a one
to many relationship. If so, can't you just bring in the primary key from
t2, as under the above assumption there will be only one matching row
from t1?

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Can I getting a unique ID from a select

2007-03-05 Thread Bruno Wolff III 
On Sat, Mar 03, 2007 at 16:46:45 -0800,
  Timasmith [EMAIL PROTECTED] wrote:
 On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote:
  On Thu, Mar 01, 2007 at 06:16:02 -0800,
   Timasmith[EMAIL PROTECTED] wrote:
 
   create view myview as
   select rownum, t1.field, t2.field
   from tableOne t1, tableTwo t2
   where t1.key = t2.fkey
 
 
 Never heard of a 'join key' but that sounds very promising.  How do I
 select it?
 

The join key would be t1.key or t2.fkey from your example. However there
may be multiple rows returned with the same value depending on what you
are joining. If that is the case you, should be able to use the primary
keys of the underlying tables to make a new candidate key for the joined
rows.

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

Re: [GENERAL] Can I getting a unique ID from a select

2007-03-04 Thread Bruno Wolff III 
On Sat, Mar 03, 2007 at 18:12:19 -0600,
  Bruno Wolff III [EMAIL PROTECTED] wrote:
 On Thu, Mar 01, 2007 at 06:16:02 -0800,
   Timasmith [EMAIL PROTECTED] wrote:
  I am using hibernate, using a view like a read only table and I need a
  primary key each time a select is issued.
  
  create view myview as
  select rownum, t1.field, t2.field
  from tableOne t1, tableTwo t2
  where t1.key = t2.fkey
  
  select * from myview
  
  But what I really need is
  
  select makemeauniquekey, t1.field, t2.field
  ...
 
 Is there some reason you can't use the join key?

To expand on this, if you are joining on fields that will return only
one record for each value, you should still be able to make a primary
key for the returned records using a combination of the primary keys
of both records being joined. If hibernate only works with primary keys
consisting of one column, than you can create a new field using a function
of the primary keys of the records being joined.

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

Re: [GENERAL] Can I getting a unique ID from a select

2007-03-04 Thread Timasmith 
On Mar 3, 7:12 pm, [EMAIL PROTECTED] (Bruno Wolff III) wrote:
 On Thu, Mar 01, 2007 at 06:16:02 -0800,
  Timasmith[EMAIL PROTECTED] wrote:

  I am using hibernate, using a view like a read only table and I need a
  primary key each time a select is issued.

  create view myview as
  select rownum, t1.field, t2.field
  from tableOne t1, tableTwo t2
  where t1.key = t2.fkey

  select * from myview

  But what I really need is

  select makemeauniquekey, t1.field, t2.field
  ...

 Is there some reason you can't use the join key?

 ---(end of broadcast)---
 TIP 4: Have you searched our list archives?

http://archives.postgresql.org/


Never heard of a 'join key' but that sounds very promising.  How do I
select it?


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

Re: [GENERAL] Can I getting a unique ID from a select

2007-03-03 Thread Bruno Wolff III 
On Thu, Mar 01, 2007 at 06:16:02 -0800,
  Timasmith [EMAIL PROTECTED] wrote:
 I am using hibernate, using a view like a read only table and I need a
 primary key each time a select is issued.
 
 create view myview as
 select rownum, t1.field, t2.field
 from tableOne t1, tableTwo t2
 where t1.key = t2.fkey
 
 select * from myview
 
 But what I really need is
 
 select makemeauniquekey, t1.field, t2.field
 ...

Is there some reason you can't use the join key?

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Can I getting a unique ID from a select

2007-03-01 Thread Bill Moran 
In response to Timasmith [EMAIL PROTECTED]:

 I am using hibernate, using a view like a read only table and I need a
 primary key each time a select is issued.
 
 So in Oracle terms this might work, though I am skeptical that
 Hibernate is going to return a cached result.
 
 create view myview as
 select rownum, t1.field, t2.field
 from tableOne t1, tableTwo t2
 where t1.key = t2.fkey
 
 select * from myview
 
 But what I really need is
 
 select makemeauniquekey, t1.field, t2.field
 ...
 
 Maybe there is no way I think...  incrementing a sequence per select
 is untenable.

Create a sequence and use nextval().

-- 
Bill Moran
Collaborative Fusion Inc.

[EMAIL PROTECTED]
Phone: 412-422-3463x4023


IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq