X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 09C9052A56 for ; Sat, 9 Apr 2005 16:43:13 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13322-05 for ; Sat, 9 Apr 2005 15:43:09 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id F0175537B8 for ; Sat, 9 Apr 2005 16:43:07 +0100 (BST) Received: by wproxy.gmail.com with SMTP id 68so1196919wri for ; Sat, 09 Apr 2005 08:43:07 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=GpsRRBue9j1dXdWpO7hcuFmnbLAE+Vnd/djiPLvRQZogIczduv3oXJhOIj0aDK22REaR7yCaHwA1UaafGODbfPWKvdK0o/64BinCdAbjC1Oc1vxaJUTJZfPyubKaWf7M5a3J4WGgEXyQaZAGpNg0Z50dZxtKt4i2dT8fJwf4ZJE= Received: by 10.54.40.34 with SMTP id n34mr491165wrn; Sat, 09 Apr 2005 08:43:07 -0700 (PDT) Received: by 10.54.70.11 with HTTP; Sat, 9 Apr 2005 08:43:07 -0700 (PDT) Message-ID: <762e5c0504090843121e087c@mail.gmail.com> Date: Sat, 9 Apr 2005 10:43:07 -0500 From: Bob Henkel Reply-To: Bob Henkel To: John DeSoi Subject: Re: getting count for a specific querry Cc: Joel Fradkin , Scott Marlowe , Andrew Sullivan , Tom Lane , pgsql-sql@postgresql.org In-Reply-To: <177B93D9-A908-11D9-92FA-000A95B03262@pgedit.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1022_33535892.1113061387437" References: <004b01c53c72$5e4c6390$797ba8c0@jfradkin> <177B93D9-A908-11D9-92FA-000A95B03262@pgedit.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.337 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_10_20, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/133 X-Sequence-Number: 21026 ------=_Part_1022_33535892.1113061387437 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Apr 9, 2005 10:00 AM, John DeSoi wrote: >=20 >=20 > On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote: >=20 > > I don't think my clients would like me to aprox as it is a count of > > their > > records. What I plan on doing assuming I can get all my other problems > > fixed > > (as mentioned I am going to try and get paid help to see if I goofed > > it up > > some where) is make the count a button, so they don't wait everytime, > > but > > can choose to wait if need be, maybe I can store the last count with a > > count > > on day for the generic search it defaults to, and just have them do a > > count > > on demand if they have a specific query. Our screens have several > > criteria > > fields in each application. >=20 > Here is an interface idea I'm working on for displaying query results > in PostgreSQL. Maybe it will work for you if your connection method > does not prevent you from using cursors. I create a cursor an then > fetch the first 1000 rows. The status display has 4 paging buttons, > something like this: >=20 > |< < rows 1 - 1000 of ? > >| >=20 > The user can hit the "next" button to get the next 1000. If less than > 1000 are fetched the ? is replaced with the actual count. They can > press the "last" button to move to the end of the cursor and get the > actual count if they need it. So here the initial query should be fast, > the user can get the count if they need it, and you don't have to > re-query using limit and offset. >=20 > The problem I'm looking into now (which I just posted on the general > list) is I don't see a way to get the table and column information from > a cursor. If I fetch from a cursor, the table OID and column number > values are 0 in the row description. If I execute the same query > directly without a cursor, the row description has the correct values > for table OID and column number. >=20 > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL Oracle Forms uses a similar method as you described and it works just fine.= =20 It will say Record 1 of ?(But I think the developer can set the amount of= =20 records cached so that if you set it to 10 and queried 5 records it would= =20 say record 1 of 5 because it would be under the cache amount.) Forms also= =20 offers a button that say get hit count. So if you really need to know the= =20 record count you can get it without moving off the current record. ------=_Part_1022_33535892.1113061387437 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On Apr 9, 2005 10:00 AM, John DeSoi <desoi@pgedit.com> wrote:

On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote:<= br>
> I don't think my clients would like me to aprox as it is a coun= t of
> their
> records. What I plan on doing assuming I can get= all my other problems
> fixed
> (as mentioned I am going to tr= y and get paid help to see if I goofed
> it up
> some where) is= make the count a button, so they don't wait everytime,
> but
>= can choose to wait if need be, maybe I can store the last count with a
= > count
> on day for the generic search it defaults to, and just h= ave them do a
> count
> on demand if they have a specific query= . Our screens have several
> criteria
> fields in each applicat= ion.

Here is an interface idea I'm working on for displaying query r= esults
in PostgreSQL. Maybe it will work for you if your connection meth= od
does not prevent you from using cursors. I create a cursor an thenfetch the first 1000 rows. The status display has 4 paging buttons,
som= ething like this:

|< < rows 1 - 1000 of ? > >|

Th= e user can hit the "next" button to get the next 1000. If less th= an
1000 are fetched the ? is replaced with the actual count. They canpress the "last" button to move to the end of the cursor and get= the
actual count if they need it. So here the initial query should be f= ast,
the user can get the count if they need it, and you don't have tore-query using limit and offset.

The problem I'm looking into now = (which I just posted on the general
list) is I don't see a way to get th= e table and column information from
a cursor. If I fetch from a cursor, = the table OID and column number
values are 0 in the row description. If = I execute the same query
directly without a cursor, the row description = has the correct values
for table OID and column number.

John DeSo= i, Ph.D.
http://pgedit.com/
Power = Tools for PostgreSQL


Oracle Forms uses a similar method as you described and it works just fine.  It will say Record 1 of ?(But I think the developer can set the amount of records cached so that if you set it to 10 and queried 5 records it would say record 1 of 5 because it would be under the cache amount.)  Forms also offers a button that say get hit count. So if you really need to know the record count you can get it without moving off the current record.

&= nbsp;

------=_Part_1022_33535892.1113061387437--