Thread

1. Queries using the C API

   Marcos Barreto de Castro <mbdecastro@yahoo.com> — 2000-05-16T12:02:52Z

   Hi,
   
     I am new to Postgresql and to SQL as well and I have
   the following situation, which has been performed so
   far using Codebase Database Manager (xbase standard)
   in
   several application softwares, using C programming
   language:
   
     I have a table with 50.000 records;
     I have to display a grid (browse) with all the
   records on the table and the user has to be able to
   scroll up and down the grid (browse), browsing
   through the records on both directions (up and down). 
     Using Codebase Database Manager, all I have to do
   is open the table (the file handle already points to
   the first record), display the current record's
   contents, skip to the next record, display its
   contents and repeat the operation until all records
   within the
   available window are displayed, leaving the file
   handle pointing to the first displayed record and
   displaying it again in a different color to show
   that it is the current selected record. If the user
   presses the down arrow key, all I have to do is skip
   to the next record using the Codebase skip function
   and
   then display the record in the selected color. If the
   user then presses the up arrow key, all I have to do
   is use the skip function again, passing an argument of
   -1 to skip 1 record backwards and display the record.
   Very
   simple indeed, isn't it? And the best part is that the
   Codebase Database Manager does all the dirty work
   through its skip function.
   
      My questions are: Is there a skip function in
   Postgresql that I can use to browse through the
   records in a table forwards and backwards (That table
   has 50,000 records)? If not, how would I be able to
   develop
   such a mechanism as the one explained above? Are there
   examples of existing algorithms or even functions,
   libraries of functions fit for the job? Are there
   examples of free software developed using the
   Postgresql C API? If so, where can I find them?
   
      Thanks a lot for any help on this matter.
   
   Marcos Barreto de Castro
   email: mbdecastro@yahoo.com
   phone: +55 61 3498747
   fax..: +55 61 3498747
   
   
   __________________________________________________
   Do You Yahoo!?
   Send instant messages & get email alerts with Yahoo! Messenger.
   http://im.yahoo.com/
   
   

2. Re: Queries using the C API

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-05-16T15:16:05Z

   Marcos Barreto de Castro wrote:
   >    My questions are: Is there a skip function in
   > Postgresql that I can use to browse through the
   > records in a table forwards and backwards (That table
   > has 50,000 records)? If not, how would I be able to
   > develop
   > such a mechanism as the one explained above? Are there
   > examples of existing algorithms or even functions,
   > libraries of functions fit for the job? Are there
   > examples of free software developed using the
   > Postgresql C API? If so, where can I find them?
   
   I may be way off base but have you looked into CURSORs?
   Apparently they can create a snapshot of the database
   and then allow you to browse backwards and forwards
   through it (FETCH i beleive).
   
   I currently thinking of using this to solve the problem
   I'm having with pg_dump dumping a 400,000 row table and
   chewing all of system memory doing it.
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/
   
   

3. Re: Queries using the C API

   Jurgen Defurne <defurnj@glo.be> — 2000-05-16T16:59:34Z

   Marcos Barreto de Castro wrote:
   
   >    My questions are: Is there a skip function in
   > Postgresql that I can use to browse through the
   > records in a table forwards and backwards (That table
   > has 50,000 records)? If not, how would I be able to
   > develop
   > such a mechanism as the one explained above? Are there
   > examples of existing algorithms or even functions,
   > libraries of functions fit for the job? Are there
   > examples of free software developed using the
   > Postgresql C API? If so, where can I find them?
   >
   
   You have to create a cursor :
   DECLARE cursor_name CURSOR
   FOR <enter your query here as a select statement> ;
   
   Your select statement will act as a filter on your tables.
   
   The declaration of the cursor (using embedded or dynamic
   SQL statements) will open the cursor.
   
   Then you can use FETCH FORWARD and FETCH BACKWARD
   to browse through the cursor.
   
   If you want to use the same cursor, but with another query,
   then you need to close it (CLOSE cursor_name), recompose your
   select statement and reopen the cursor.
   
   I know xBase, and I have even used CodeBase (but that was 10
   years ago). You will probably have to work somewhat harder on
   using postgreSQL, although if your browse infrastructure good,
   you could probably write a similar interface to postgreSQL as is
   used in CodeBase.
   
   Good luck,
   
   Jurgen Defurne
   defurnj@glo.be