PostgreSQL - PL/pgSQL UDF example

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:

CREATE OR REPLACE FUNCTION create_voucher(int)
  RETURNS void AS
$BODY$

DECLARE
	count ALIAS FOR $1;
	_cnt int := 1;
	_serial int;

BEGIN
	SELECT max(vserial) INTO _serial FROM vouchers;
	_serial := _serial +1;

	WHILE _cnt <= count LOOP
		INSERT INTO vouchers (vserial,created,vcode,title,percent) VALUES
		(_serial,NOW(),lpad(_cnt::varchar, 4, '0'),'30% voucher',30);
		_cnt := _cnt + 1;
	END LOOP;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

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