Re: PostgreSQL Advocacy, Thoughts and Comments

On 29/11/03, Randal L. Schwartz (merlyn(at)stonehenge(dot)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>