Thread

extension to SQL to access TIDs from indices

Jan Vicherek <honza@ied.com> — 1998-01-25T06:00:59Z

   hi,

 I'm tempted to implement an extension to the current postgres' SQL
language that would allow me to do something like :

  CREATE INDEX MyIndex ON MyTable (IndexField);
 BEGIN;
  SELECT LAST(Tid) AS MyFirstTid FROM MyIndex WHERE IndexField = 'what_user_requests';
  SELECT * from MyTable where Tid = MyFirstTid;
 END;
 ... disconnect ...
 ... reconnect ...
 BEGIN;
  SELECT NEXT(Tid) AS MyNextTid FROM MyIndex WHERE Tid = MyFirstTid; 
  SELECT * from MyTable where Tid = MyNextTid;
 END;
 ... disconnect ...
  ... etc ...

  The "LAST(Tid)" could also be "FIRST(Tid)". The FIRST/LAST keywords are
there so it knows which index pointer (TID) to pick when the index isn't
unique and the 'what_user_requests' value happens to be in the index more
than once.

  The NEXT could also be PREVIOUS.

   Now I understand that the first and second SELECT would have to be in
one transaction, so that other users cannot invalidate on me (delete /
update) the record that the TID points to. 

   Also after I reconnect, it is not guaranteed that MyFirstTid is still a
valid Tid. If it is not valid, then the first SELECT should return 0 rows
as result. If it is still valid, it should return exacly one row with the
next TID in the index.


 I would appreciate opinions on any of the following :

  o. is this a reasonable approach to solve my "browsing" problem ?

  o. what would be involved in implementing this ?

  o. given the current design, how many lines (estimate) of code would
have to be written to do this,

  o. given the current design, how many functions (estimate) would have to
be rewritten

   Is there anybody out there who would be willing to do this with me over
the next couple of days ? (My "selling" of postgres to my customer is
completely lost yet.)

   Or is there anybody who could at least feed me with pointers when I get
stuck ? (I.e. you would give me the direction(s), I would do the work.)


       Thanx,

             Jan


 -- Gospel of Jesus is the saving power of God for all who believe --
Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
    >>>    Free Software Union President  ...  www.fslu.org    <<<
Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com