Friday, November 26, 2010

new version of pst collection is relased

Hello
I released a updated version of pst collection. What is a pst collection? It is a collection of usable functions for PostgreSQL valuable for my work. Some from these function will be in next version of PostgreSQL, others not. You can download pst collection from http://pgfoundry.org/frs/download.php/2890/pstcoll-10-11-27.tgz pgfoundry. What you can find there: formatting functions - sprintf, format (with same behave like buildin function from 9.1), left, right, reverse, concat, concat_ws, concat_js, concat_sql. Parse functions - chars_to_array, diff_string, lc_substring. Date function - next_day and last_day. Utility function - counter. Some examples:

pavel=# set search_path = 'pst';
SET
Time: 0.411 ms
pavel=# select sprintf('|%10s|', 'hello');
   sprintf    
──────────────
 |     hello|
(1 row)

pavel=# select sprintf('|%-10s|', 'hello');
   sprintf    
──────────────
 |hello     |
(1 row)

pavel=# select format('INSERT INTO %I VALUES(%L)', 'tabname', 'some value');
                  format                  
──────────────────────────────────────────
 INSERT INTO tabname VALUES('some value')
(1 row)

pavel=# select concat_js(10,22,'weqwe',true, current_date);
            concat_js            
─────────────────────────────────
 10,22,"weqwe",true,"2010-11-26"

pavel=# select chars_to_array('příliš žlutý kůň');
            chars_to_array             
───────────────────────────────────────
 {p,ř,í,l,i,š," ",ž,l,u,t,ý," ",k,ů,ň}
(1 row)

pavel=# select diff_string('Ahoj','Ahojky');
  diff_string   
────────────────
 Ahoj<ins>ky</>
(1 row)

pavel=# select diff_string('Ahojky','Ahoj');
  diff_string   
────────────────
 Ahoj<del>ky</>
(1 row)

pavel=# select diff_string('red dog and cat','green dog and horse');
                           diff_string                           
─────────────────────────────────────────────────────────────────
 <ins>g</>re<del>d</><ins>en</> dog and <del>cat</><ins>horse</>
(1 row)

pavel=# create table target(a int);
CREATE TABLE
Time: 53.819 ms
pavel=# insert into target select counter(i, 5000, true) from generate_series(1,20000) g(i);
NOTICE:  processed 5000 rows, current value is '5000'
NOTICE:  processed 10000 rows, current value is '10000'
NOTICE:  processed 15000 rows, current value is '15000'
NOTICE:  processed 20000 rows, current value is '20000'
INSERT 0 20000
Time: 1170.040 ms
I hope so these function will by useful for you too.

This package should to work on PostgreSQL 8.4, 9.0
Pavel