Andreas Wenk
01.07.09

PostgreSQL 8.4 released today

Moin Moin,

finally the PostgreSQL 8.4 database was released today. Let's open a bottle of champaign and enjoy the new features.

Cheers

Andy

P.S.: yes - the image is the link ...

Andreas Wenk
01.07.09

balcony swiming pool

Moin Moin,

well - it's 11:00 am and we have nearly 30 C celsius her in Hamburg. And my daughter Kiana doesn't need to go to the swimming bath because we have a swiming pool on our balcony ;-)

Have a nice day

Andy

Andreas Wenk
29.06.09

PGDAY 2009

Moin Moin,

after some days holiday and a great festival time at Hurricane Festival in Scheßel (Germany), I am back at work and writing.

This time I want to announce the PGDAY 2009 in Paris this year. You will find all info at 2009.pgday.eu . Call for papers is open now.

Maybe we meet there ...

Cheers

Andy

Andreas Wenk
18.06.09

interesting links (imo)

Moin Moin,

when I find an interesting resource or receive a link for example via twitter, I store it somwhere on my desktop. "Why don't you use delicious?" - because ... what the heck. I don't know.

I decided to have a look at all the collected links and post the best here. A lot of them will be allready well known. And again I have my own convenience in mind ... ;-) The following list is extremly unsorted!

A nice extension for uploading and managing files in the WYSIWYG editor tinyMCE:
http://www.lunarvis.com/products/tinymcefilebrowserwithupload.php

A german book about PHP5:
http://www.professionelle-softwareentwicklung-mit-php5.de/

PHP doc for DOM / Xpath:
http://de.php.net/manual/en/domxpath.query.php

Built MAC with Ruby:
http://www.macruby.org/

Nice MAC style coverflow in JavaScript:
http://www.netzgesta.de/reflex/

Create PDF files with PHP5:
http://www.tecnick.com/public/code/cp_dpage.php?aiocp_dp=tcpdf

XPATH tutorial:
http://www.zvon.org/xxl/XPathTutorial/Output_ger/example1.html

jQuery tutorials:
http://www.learningjquery.com/

LightningFastShop written in Python form [Twitter] @diefenbach
http://www.getlfs.com/features

Standardization of ontologies from Kore Nordmann:
http://kore-nordmann.de/blog/0089_standardization_of_ontologies.html

jQuery performance rules:
http://www.artzstudio.com/2009/04/jquery-performance-rules/

open database alliance:
http://opendatabasealliance.com/

a jQuery chat:
http://anantgarg.com/2009/05/13/gmail-facebook-style-jquery-chat/

online database modelling tool (also free version available):
http://schemabank.com/

Ruby - CouchDB with CouchRest in 5 minutes:
http://merbist.com/2009/05/17/couchdb-with-couchrest-in-5-minutes/

the Git parable:
http://tom.preston-werner.com/2009/05/19/the-git-parable.html

jQuery namespaces:
http://jquery-howto.blogspot.com/2009/01/namespace-your-javascript-function-and.html

secure cookie authentication in CouchDB by Jason Davies:
http://www.jasondavies.com/blog/2009/05/27/secure-cookie-authentication-couchdb/

parsing strings with jQuery:
http://devkick.com/blog/parsing-strings-with-jquery/

autopsy - recover lost data:
http://www.sleuthkit.org

PostgreSQL Python connectors:
http://python.projects.postgresql.org/
http://barryp.org/software/bpgsql/
http://www.pygresql.org/
http://pypgsql.sourceforge.net/
http://initd.org/pub/software/psycopg/
http://www.python.org/dev/peps/pep-0249/
http://wiki.python.org/moin/UsingDbApiWithPostgres#python-db-api

hosting git repositorys:
http://scie.nti.st/2007/11/14/hosting-git-repositories-the-easy-and-secure-way

german Debina Handbuch:
http://debiananwenderhandbuch.de/

hm - quite a lot ... but mybe of interest for you.

Andy

Andreas Wenk
15.06.09

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:

Code:

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

1 2 3 4 5 6 7 8 9 10 11 ... 15 >>

PGDAY

Juli 2009
Mo Di Mi Do Fr Sa So
 << <   > >>
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31    

Bookmark

Bookmark and Share

Books

actually empty ;-)

Blog Verzeichnis

Blogverzeichnis - Blog Verzeichnis bloggerei.de
Bloggeramt.de
Blog Flux Directory
powered by b2evolution free blog software