Thread: Count and Results together
Hi
Is possible use SELECT NUM_ROWS, a, b, c FROM blabla WHERE x =id; I mean
it's much more faster than
SELECT a, b, c FROM blabla WHERE x =id; and then
SELECT count(*) FROM blabla WHERE x =id;
John
On May 18, 2005, at 3:07 AM, Jan Sunavec wrote: > Hi > > Is possible use SELECT NUM_ROWS, a, b, c FROM blabla WHERE x =id; I > mean it's much more faster than > > > SELECT a, b, c FROM blabla WHERE x =id; and then > SELECT count(*) FROM blabla WHERE x =id; Can you do this on the client side? What client/interface are you using? There is a nice archive of the postgres mailing lists here: http://archives.postgresql.org/ A quick search for ranks (not exactly what you want, but I think it has a useable solution in one of the posts) gets: http://archives.postgresql.org/pgsql-general/2005-05/msg00157.php
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?
JOhn
On Wed, 18 May 2005 17:37:09 +0200, Sean Davis <sdavis2@mail.nih.gov>
wrote:
>
> On May 18, 2005, at 3:07 AM, Jan Sunavec wrote:
>
>> Hi
>>
>> Is possible use SELECT NUM_ROWS, a, b, c FROM blabla WHERE x =id; I
>> mean it's much more faster than
>>
>>
>> SELECT a, b, c FROM blabla WHERE x =id; and then
>> SELECT count(*) FROM blabla WHERE x =id;
>
> Can you do this on the client side? What client/interface are you using?
>
> There is a nice archive of the postgres mailing lists here:
> http://archives.postgresql.org/
>
> A quick search for ranks (not exactly what you want, but I think it has
> a useable solution in one of the posts) gets:
> http://archives.postgresql.org/pgsql-general/2005-05/msg00157.php
>
>
On Thu, May 19, 2005 at 08:58:13AM +0200, Jan Sunavec 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? You're missing the point. When you do a query in PostgreSQL you know how many rows were returned. The interface tells you. For example in Perl it's $sth->rows for DBI or $res->ntuples for Pg, in pl/PgSQL it's GET DIAGNOSTICS, in C it's PQntuples(). The rowcount is metadata and is passed seperately from the data. The interface you're using should tell you how many rows there are... Perhaps you should tell us what language/interface you are using so we can give a complete answer. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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
On 5/19/05, Dawid Kuroczko <qnex42@gmail.com> wrote:
> 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...
I stand corrected. GET DIAGNOSTICS, PQntuples(), etc. are the way
to do it.
Regards,
Dawid
jan.sunavec@gmail.com ("Jan Sunavec") writes:
> I am using libpg.so.
I assume that you mean libpq ?
>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?
>
Have you read your documentation? Perhaps this page would be of
interest to you?
http://www.postgresql.org/docs/8.0/interactive/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO
--
Remove -42 for email