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:
Show quoted text
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
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.
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?