Thread: Queries using the C API

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/

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/

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