Re: PostgreSQL Advocacy, Thoughts and Comments - Mailing list pgsql-general

On 29/11/03, Randal L. Schwartz (merlyn@stonehenge.com) wrote:
> Well, since I need 2.5 ideas per month for the three columns I'm still
> writing, I'm certainly in a position to write nice things about PG,
> although I always have to work it in from a Perl slant.
>
> Actually, I'm sure that any of the magazines I'm in would appreciate
> an additional article or two from me.
>
> If you can think of something that fits in 2000 words or so (or 4000
> if it needs part 1 and 2), and can have a Perl wrapper, I'd appreciate
> some inspiration.

Hi Randal

I think I may have an idea for an article which would address a common
problem for people writing database client interfaces:

The problem is simply explained.

Problem title:

    The "page of pages" problem (!)

The problem:

    You want to return a subset of a large number items using some
    fairly complex search criteria. You want to make only one database
    call, benefit from a cached query, and don't want to have all the
    rows in memory. How do you get the total count of pages for the
    relevant search criteria?

Why is this relevant?

    Moving logic that is inherent to the database to the database
    provides a potentially rich yet simple interface to database
    queries that can benefit a number of client applications.

    Typically this sort of query would be written as at least two
    dynamically generated queries in the client program that has to be
    parsed by the backend before it is executed. By using functions we
    can hide complex joins behind simple field names, and provide
    flexible (if limited) search capabilites, as well as caching and
    sensible error messages.

Approach:

    Using Postgres one can construct a function and then do either

       SELECT * from function fn_explore($searchstring, $limit, $offset);
    OR
       SELECT
        *
       FROM
        function fn_explore()
       WHERE
        searchterm ~* 'test'
       LIMIT
        5
       OFFSET
        10;

    What is cool about the second format is that (if the function
    returned a type 'explore_result' as below), your PHP/Perl programmer
    can at their interface do something like

        '... where id <> 1 AND author IN ('james', 'bill')...'

    However I don't know how you get back the total rows in this case,
    also maybe the caching effects are minimised?

    --------------------------------------------------------------------
    Type definition:

    CREATE TYPE explore_result as (
        id                 INTEGER,   -- some sort of row id
        total              INTEGER,   -- total rows for query
        author             VARCHAR,
        image              BYTEA
        /*
        Not needed unless search is done outside db.
        , searchterm         VARCHAR
        */
    );

    --------------------------------------------------------------------
    Sketch function definition:

    CREATE OR REPLACE FUNCTION
        fn_explore (integer, integer, integer) RETURNS setof explore_result
    AS '
    DECLARE
        searchstring  ALIAS for $1;
        offsetter     ALIAS for $2;
        limiter       ALIAS for $3;
        resulter      explore_page%rowtype;
    BEGIN

        /*
          variable verifation section chopped
        */

        FOR resulter IN
            SELECT
                n_id                                          as id,
                LOJ.pagetotal                                 as total
                pers.t_name                                   as author,
                image.b_contents                              as image
                /*
                need searchterm returned if we are doing search outside
                the database
                , COALESCE(t_title || '' '' || t_text,  '''') as searchterm

            FROM
                db
                /*
                -> self join on db LOJ for unoffset, unlimited row count
                   refer to searchterm stuff below
                */
            WHERE
                /* note, if we are doing a search outside of the
                 * function and t_title or t_text could be empty then we
                 * need to coalesce to an empty string
                 * COALESCE(t_title || '' '' || t_text,  '''') as searchterm
                 */
                 searchstring ~ t_title || '' '' || t_text
            ORDER BY
                dt_modified DESC
            LIMIT
                limiter
            OFFSET
                offsetter
                ,

             LOOP

            RETURN NEXT
                resulter;

        END LOOP;

        RETURN;
    END;'
        LANGUAGE plpgsql;

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>