Re: 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(at)postgresql(dot)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

A long time ago, in a galaxy far, far away, orbit(at)0x63(dot)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.

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

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?