It has been my custom to include an editorial or a survey every ten issues. And now it has been 50 weeks that this column has been published. This is almost a full year of publication. I hope you have found that the quality has improved over this time.

I celebrate the addition of both Portuguese and Spanish language translations. Juliano da Silva and Alvaro Herrera have taken on a difficult task and have done a marvelous job helping to spread the word to their communities.

In the future, I would like to solicit more contributions of functions, schemas and tips from you directly. I will be expanding the Tidbits archives to include more of the code from the various issues. I would like Tidbits to be a good place to look for examples of how to do interesting things.

Two examples of contributions are in this issue. The first is a How To guide and the second is a useful plpgsql function. By sharing both various documents, like Josh's performance documents, and annotated code, like Stephan Szabo's Set Returning Functions examples, I hope to provide a rich resource for PostgreSQL.

Collaboration is the word of the week. Comments, suggestions, documents and code samples can all be submitted to elein.

Editor: elein at varlena.com

Debian eRServer How To [Erserver-general] Erserver Debian HOWTO URL 24-Oct-2003

Ivailo T. Ivanov of Mag Studio has written a brief eRServer Debian How To guide. It will provide you with a good start around some of the initial problems with installation with Debian. It is also available from the Tidbits area.

Editor's Note: Full sequence of steps were not tested

Contributors: Ivailo Toshev Ivanov ivo at magstudio.net

Credit Card Validation [sfpug] [cool hack] Credit Card validator 30-Oct-2003

David Fetter has ported the Business::CreditCard module into plpgsql. This is very useful if you are not able to use plperl for any reason in your database and still need to do credit card validation. The evaluation of the card numbers is also a good example of how to use the ~ operator.

This is a good example of the kind of processing necessary for business applications that can be done in the server. As many applications access the database, it is good to have a database centered validation routine so that all of the application do the validation consistently.

CREATE OR REPLACE FUNCTION cc_check (VARCHAR) RETURNS BOOLEAN AS '
DECLARE
    cc_num     ALIAS FOR $1;
    i          INTEGER;
    d          VARCHAR;
    weight     INTEGER;
    checksum   INTEGER := 0;
    oddness    INTEGER;
    num        INTEGER;
    tempstring VARCHAR;
BEGIN
    -- Strip out non-digits.  Oh, for an s/// construct! :)
    tempstring := '''';
    FOR i IN 1 .. length(cc_num)
    LOOP
        d := SUBSTRING(cc_num, i, 1);
        IF d ~ ''[0-9]''
        THEN
            tempstring := tempstring || d;
        END IF;
    END LOOP;
    -- Check length for reasonableness.
    IF (length(tempstring) < 13)
    THEN
        RAISE NOTICE ''% is too short to be a credit card number.'', cc_num;
        RETURN FALSE;
    END IF;
    -- Do a Luhn checksum.
    oddness := length(tempstring) % 2;
    FOR i IN 1 .. length(tempstring)
    LOOP
        num := SUBSTRING(tempstring, i, 1)::integer;
        IF (oddness = 0 AND i % 2 = 1)
        THEN
            num := num * 2;
        ELSIF (oddness = 1 AND i%2 = 0)
        THEN
            num := num * 2;
        END IF;
        IF num > 9
        THEN
            num := num - 9;
        END IF;
        checksum := checksum + num;
    END LOOP;
    IF checksum % 10 <> 0
    THEN
        RAISE NOTICE ''% does not have a valid checksum.'', cc_num;
        RETURN FALSE;
    END IF;
    IF tempstring ~ ''^3[47][0-9]{13}$''
    THEN
        RAISE NOTICE ''% is an American Express card.'', cc_num;
    ELSIF tempstring ~ ''^4[0-9]{12}([0-9]{3})?$''
    THEN
        RAISE NOTICE ''% is a VISA card.'', cc_num;
    ELSIF tempstring ~ ''^5[1-5][0-9]{14}$''
    THEN
        RAISE NOTICE ''% is a MasterCard.'', cc_num;
    ELSIF tempstring ~ ''^6011[0-9]{12}$''
    THEN
        RAISE NOTICE ''% is a Discover card.'', cc_num;
    ELSIF tempstring ~ ''^3(0[0-5]|[68][0-9])[0-9]{11}$''
    THEN
        RAISE NOTICE ''% is a Diners Club/Carte Blanche.'', cc_num;
    ELSIF tempstring ~ ''^2(014|149)\d{11}$''
    THEN
        RAISE NOTICE ''% is an enRoute card.'', cc_num;
    ELSIF tempstring ~ ''^(3\d{4}|2131|1800)\d{11}$''
    THEN
        RAISE NOTICE ''% is a JCB card.'', cc_num;
    ELSIF tempstring ~ ''^56(10\d\d|022[1-5])\d{10}$''
    THEN
        RAISE NOTICE ''% is a BankCard.'', cc_num;
    END IF;
    RETURN TRUE;
END;
' LANGUAGE 'plpgsql';

Contributors: David Fetter david at fetter.org

A Few Optimization Questions Answered [GENERAL] formatting of SQL sent by PHP to postgres 30-Oct-2003

Question: Is the semicolon at the end of SQL superflous when sent to Postgres from PHP?
Answer: Yes, you can do without the semicolon when sending statements through PHP.

Question: Does the large amount of whitespace generated in PHP SQL statements have an effect on the speed of the query?
Answer: No. It is probably unmeasureable unless there are kilobytes of spaces.

Question: How can I speed up

select max(id) from table;

select id from table order by id desc limit 1;

Question: Why is one faster than the other? They are the same on SQLServer.
Answer: Due to MVCC, PostgreSQL doesn't use the index for aggregation so max() is slower than the sort which does use the index. Note that this only works when there are no NULLs in the column and there is an index on the column.

For general information and discussion on PostgreSQL Performance, join the pgsql-performance mailing list.

Contributors: Ken Guest kguest@stockbyte.com, scott.marlowe scott.marlowe@ihs.com, David Green david@sagerobot.com, Robert Treat xzilla@users.sourceforge.net, Greg Stark gsstark@mit.edu, Hervé Piedvache herve@elma.fr, Tom Lane tgl at sss.pgh.pa.us

Perl Modules for PostgreSQL [GENERAL] Best Perl Option? 30-Oct-2003

The best perl module for PostgreSQL is DBD::Pg. now hosted on gborg. Pg.pm is also available on gborg or via CPAN.

Contributors: David Busby busby at pnts.com Doug McNaught doug at mcnaught.org Network Administrator netadmin at vcsn.com