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 msI hope so these function will by useful for you too.
This package should to work on PostgreSQL 8.4, 9.0
Pavel
Pavel,
ReplyDeleteI'm still running 8.4.5, so linking strings.so failed. In append_string, I had to replace both occurrences of appendStringInfoSpaces with:
appendStringInfo(str, "%*s", pdesc->width - nchars, " ");
I ran the examples on your blog without apparent error. Will you please verify that what I've done is correct?
yes, it's developed for 9.0, but isn't problem to run it on 8.4. I uploaded updated version
ReplyDelete