Thread: retrieving parts of a resultset

I want to create a program which displays large tables and makes this possible
over a slow connection. The problem is that when i do a PQexec the entire
retultset is transfered.

I would like to make pqsql process the query but only tranfer the the rows i
ask for when i ask for them. This way i could transfer just the information
currently displayed and not the entire result.

Is this possible or do i have to do a (create temp table as select ...) and
then do (select ... limit ..) in this temporary table?
This would work but i dont think it's a very good solution.

 / Christoffer Gurell

I think you should use a cursor; you declare it, and then you fetch the rows as you need them.

On Fri, 2004-02-06 at 14:04, Christoffer Gurell wrote:

I want to create a program which displays large tables and makes this possible
over a slow connection. The problem is that when i do a PQexec the entire
retultset is transfered. 

I would like to make pqsql process the query but only tranfer the the rows i 
ask for when i ask for them. This way i could transfer just the information 
currently displayed and not the entire result. 

Is this possible or do i have to do a (create temp table as select ...) and 
then do (select ... limit ..) in this temporary table? 
This would work but i dont think it's a very good solution. 
/ Christoffer Gurell

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to majordomo@postgresql.org so that your     message can get through to the mailing list cleanly

> I think you should use a cursor; you declare it, and then you fetch the
> rows as you need them.

thanks this works really nice.. just one more question .. how do i check the
number of rows in a cursor? or do i have to do a select count(*) on the query
i use to create the cursor?

 / Christoffer Gurell

On Fri, Feb 06, 2004 at 02:31:38PM -0300, Franco Bruno Borghesi wrote:
> I think you should use a cursor; you declare it, and then you fetch the
> rows as you need them.

thanks this works really nice.. just one more question .. how do i check the
number of rows in a cursor? or do i have to do a select count(*) on the query
i use to create the cursor?

  / Christoffer Gurell

A long time ago, in a galaxy far, far away, orbit@0x63.nu (Christoffer Gurell) wrote:
>> I think you should use a cursor; you declare it, and then you fetch the
>> rows as you need them.
>
> thanks this works really nice.. just one more question .. how do i check the
> number of rows in a cursor? or do i have to do a select count(*) on the query
> i use to create the cursor?

Make sure that the count(*) query takes place in the scope of the same
transaction, and that you SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
in that transaction, otherwise the count(*) query may find different
results...
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/finances.html
Nobody can fix the economy.  Nobody can be trusted with their finger
on the button.  Nobody's perfect.  VOTE FOR NOBODY.

I'm flummoxed on this one.  I have a class that's building a query which
selects data from 1-n tables based on a common indexed id, io_id.  These
tables may contain 1-n rows of data keyed on io_id.  What I want the
query to do is return nulls for replicated columns rather than just
replicating them.

Here's the (relevant) data:

opt_io_vegetables_id:
  id | io_id | opt_val
----+-------+---------
  27 |   274 |       1
  28 |   274 |       3
  29 |   274 |       5
  30 |   274 |       7

opt_io_fruits_id:

  id | io_id | opt_val
----+-------+---------
  12 |   274 |       9


opt_io_name_text:

  id | io_id |             opt_val
----+-------+---------------------------------
  12 |   274 | Text... text... text... text...

I have this query:

SELECT
     A.opt_val,
     B.opt_val,
     C.opt_val
FROM
     IO io
     INNER JOIN opt_io_vegetables_id A ON io.id = A.io_id
     INNER JOIN opt_io_fruits_id B ON io.id = B.io_id
     INNER JOIN opt_io_name_text C ON io.id = C.io_id
WHERE
     io.id = 274;

It returns:

  opt_val | opt_val |             opt_val
---------+---------+---------------------------------
        1 |       9 | Text... text... text... text...
        3 |       9 | Text... text... text... text...
        5 |       9 | Text... text... text... text...
        7 |       9 | Text... text... text... text...

What I'd *like* the query to do for the replicated columns in $col[1]
and $col[2] is return nulls.

Is there any way to do this?