On 5/19/05, Jan Sunavec <jan.sunavec@gmail.com> wrote:
> I am using libpg.so. I tryed find solution for this problem in internet
> but, I don't find nothing yet. I have idea get rowcount throught some
> function write in C. Or is there any plan add this feature into PostgreSQL?
Theoretically you could declare a cursor, count the rows (from plpgsql
preferably, to avoid sending all the data to the client), rewind the
cursor (MOVE) and return it. The problem is that I think it is not possible
for PL/pgsql to return both integer (row count) and a cursor (for the
query rewound) at the same time...
Alternatively you could make such a trick, but this won't work (moving
inside cursor seems to cause the nextval() to reevaluate. Anyway
the idea would be:
BEGIN;
CREATE TEMPORARY SEQUENCE rowcount;
DECLARE thedata SCROLL CURSOR FOR SELECT nextval('rowcount'), * FROM tbl;
MOVE ABSOLUTE -2 IN thedata; -- get the last row...
FETCH thedata; -- the nextval column should contain the row count.
MOVE ABSOLUTE 1 IN thedata; -- rewind the cursor
FETCH, fetch, fetch or FETCH ALL...
DROP SEQUENCE...
COMMIT or maybe even ROLLBACK;
Though looking promising, the problem is that nextval() seems to
be reevaluated for each row... [ Is it the way it should be? ]
Other rather silly ideas:
create temporary table (preferably within transaction with on commit
drop or truncate) with index on rowcount column. select results into
this table (with row counter done with help of the sequence). select
rowcount from temptable order by rowcount desc limit 1; select * from
temptable;
Overkill but for complex queries it might do a trick. Then again,
such caching-table might be useful for serving "search results" by
many Apache daemons...
HTH,
Daiwd