Find original number of rows before applied LIMIT/OFFSET?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • D. Dante Lorenso

    #1

    Find original number of rows before applied LIMIT/OFFSET?


    I need to know that original number of rows that WOULD have been returned
    by a SELECT statement if the LIMIT / OFFSET where not present in the
    statement.
    Is there a way to get this data from PG ?

    SELECT
    ... ;

    ----> returns 100,000 rows

    but,

    SELECT
    ...
    LIMIT x
    OFFSET y;

    ----> returns at most x rows

    In order to build a list pager on a web site, I want to select 'pages' of a
    result set at a time. However, I need to know the original select
    result set
    size because I still have to draw the 'page numbers' to display what
    pages are
    available.

    I've done this TWO ways in the past:

    1) TWO queries. The first query will perform a SELECT COUNT(*) ...; and
    the second query performs the actualy SELECT ... LIMIT x OFFSET y;

    2) Using PHP row seek and only selecting the number of rows I need.

    Here is an example of method number 2 in PHP:

    //----------------------------------------------------------------------
    function query_assoc_pag ed ($sql, $limit=0, $offset=0) {
    $this->num_rows = false;

    // open a result set for this query...
    $result = $this->query($sql);
    if (! $result) return (false);

    // save the number of rows we are working with
    $this->num_rows = @pg_num_rows($r esult);

    // moves the internal row pointer of the result to point to our
    // desired offset. The next call to pg_fetch_assoc( ) would return
    // that row.
    if (! empty($offset)) {
    if (! @pg_result_seek ($result, $offset)) {
    return (array());
    };
    }

    // gather the results together in an array of arrays...
    $data = array();
    while (($row = pg_fetch_assoc( $result)) !== false) {
    $data[] = $row;

    // After reading N rows from this result set, free our memory
    // and return the rows we fetched...
    if (! empty($limit) && count($data) >= $limit) {
    pg_free_result( $result);
    return ($data);
    }
    }

    pg_free_result( $result);
    return($data);
    }

    //----------------------------------------------------------------------

    In this approach, I am 'emulating' the LIMIT / OFFSET features in PostgreSQL
    by just seeking forward in the result set (offset) and only fetching the
    number of rows that match my needs (LIMIT).

    QUESTION: Is this the best way to do this, or is there a more efficient way
    to get at the data I want? Is there a variable set in PG that tells me the
    original number of rows in the query? Something like:

    SELECT ORIG_RESULT_SIZ E, ...
    ...
    LIMIT x
    OFFSET y;

    Or can I run another select right afterwards...li ke:

    SELECT ...
    ...
    LIMIT x
    OFFSET y;

    SELECT unfiltered_size _of_last_query( );

    Any thoughts? Sure, the PHP function I'm using above 'works', but is it
    the most efficient? I hope I'm not actually pulling all 100,000 records
    across the wire when I only intend to show 10 at a time. See what I'm
    getting at?

    TIA,

    Dante

    ---------
    D. Dante Lorenso
    dante@lorenso.c om


    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

Working...