By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,275 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,275 IT Pros & Developers. It's quick & easy.

retrieving parts of a resultset

P: n/a
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (FreeBSD)

iD8DBQBAI89521dVnhLsBV0RAhRoAJ4iuEbsPVMocFheU9gNhe 9wsIDyrACffPx6
/RExJ3iA3vuQpC6Xv0p8258=
=XDwt
-----END PGP SIGNATURE-----

Nov 22 '05 #2

P: n/a
> 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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #3

P: n/a
A long time ago, in a galaxy far, far away, or***@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.
Nov 22 '05 #4

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #5

P: n/a
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?

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.