Netzmeister St.Pauli

Moin moin,

actually one of our customers was asking if I can help him out with creating some vouchers for the online shop we made for him. You can visit the shop here: fashionroom.biz. The shop is - for sure - based on a PostgreSQL database. We did include a voucher mechanism but he was printing vouchers with numbers from 0001 up to 2500. The shop system does create random voucher codes like AYXVFY so I told him (in a joke) that he has a nice job now to change the 2500 voucher codes ;-).

There are tow ways to create these vouchers. One would be to use PHP and fire 2500 INSERT querys into the database. Sure, as a prepared statement, it would be possible to avoid a load peak. But writing the script, transferring it to the server and so on ... na! Annoying. So I decided to use a user defined function (UDF) written in PL/pgSQL. It's quite easy but shows some features of udf's in PL/pgSQL. Here it is:

Code:

As I said, it's quite easy.

The parameter in $1 is the max code number of the vouchers. vserial is a serial to put different vouchers together. It's different to the id of the table which is serial (not shown here). So that's the reason why I have to use the SELECT INTO query.
_cnt is just a counter used inbetween the while loop and is increased by 1 each turn. Quite nice is the usage of lpad(). The vcode column is of datatype varchar. Here I simply cast the _cnt variable to a varchar and give it to lpad(). lpad() is now creating strings like 0001, 0015, 0425 and 2500. It fills 0's from left to right until the string has a length of 4. Yeah - perfect.

So that's it. I think it's much faster to use this instead of PHP.

Andreas