PL toolbox (en)

Pl toolbox is a module with functions that are designed for auxiliary functions in the development in plpgsql. Some of the features of this module as an embedded feature in PostgreSQL 9.1 (some features to work with strings). Most of the other is not in PostgreSQL, and so far not to be classified. All functions are placed into the scheme PST. The source code of the module can be downloaded from http://pgfoundry.org/frs/?group_id=1000457 . It's available for PostgreSQL 8.4, 9.0, 9.1, 9.2 and 9.3

String's functions

sprintf(formatstr [, values])	see man sprintf
format(formatstr [, values])	Substitutes symbols corresponding parameter value. Symbols are supported -% s string, and SQL% ID% L SQL literal (string).
concat(values)	Joins parameters into one string
concat_ws(separator, values)	Joins parameters into one string with separator
concat_sql(values)	Creates a list (comma-separated values) in SQL format
concat_js(values)	Creates a list (comma-separated values) in JSON format
left(string, nchars)	Returns the first n characters (from the left), if n is negative, the returns from left without the last n characters
right(string, nchar)	Returns the last n characters (from the right), if n is negative, the returns from right without the first n characters
reverse(string)	Swaps the characters in the string
chars_to_array(string)	Spread over a string to a character array
diff_string(string_a, string_b)	Returns the formated difference between two strings

pavel=# select pst.sprintf('>>%010.4f<<', 3.14);
    sprintf     
----------------
 >>00003.1400<<
(1 row)

pavel=# select pst.format('INSERT INTO %I VALUES(%L,%L)', 'my tab', NULL, 'Hello');
                  format                   
-------------------------------------------
 INSERT INTO "my tab" VALUES(NULL,'Hello')
(1 row)

pavel=# select pst.concat_js(10, 'Hello', true, current_date);
          concat_js           
------------------------------
 10,"Hello",true,"2010-12-30"
(1 row)

pavel=# select pst.chars_to_array('Hello');
 chars_to_array 
----------------
 {H,e,l,l,o}
(1 row)

pavel=# select pst.diff_string('Hello World','Hello');
     diff_string     
---------------------
 Hello<del> World</>
(1 row)

Date's functions

next_day(date, weekday)	Return the first day of the week after the specified day
last_day(date)	Returns the last day of the month

pavel=# select next_day(to_date('2010-12-31','YYYY-MM-DD'), 'mon');
  next_day  
------------
 2011-01-03
(1 row)

pavel=# select last_day(to_date('2010-12-31','YYYY-MM-DD'));
  last_day  
------------
 2010-12-31
(1 row)

Record's functions

record_expand(rec)	Converts composite-type to table
record_get_field(rec, fieldname)	Returns the selected item
record_get_fields(rec, fieldname, ..)	Returns the selected items
record_set_fields(rec, fieldname, value, ..)	Returns the modified record, requested changes are always entered as a pair: name, value

create or replace function omega_trg_fce()
returns trigger as $$
declare r record;
begin
  for r in select * from record_expand(new)
  loop
    raise notice 'name := "%", value := "%"', r.name, r.value;
  end loop;
  return new;
end;
$$ language plpgsql;

create trigger xxx before insert on omega for each row execute procedure omega_trg_fce();

pavel=# select * from pst.record_expand(row(10,20));
 name | value |   typ   
------+-------+---------
 f1   | 10    | integer
 f2   | 20    | integer
(2 rows)

pavel=# select * from pst.record_expand(pst.record_set_fields(row(10,20),'f1',33));
 name | value |   typ   
------+-------+---------
 f1   | 33    | integer
 f2   | 20    | integer
(2 rows)

Bitmapset's functions

It introduce a new datatype - pst.bitmapset.

add_member(bitmapset, integer)	Adds a number to set
add_members(bitmapset, integer, ..)	Allows you to add more numbers to the set
del_member(bitmapset, integer)	Delete the number from the set
del_members(bitmapset, integer, ..)	Delete the numbers from the set
is_member(bitmapset, integer)	Checks whether the number is a member of the set
bitmapset_union(bitmapset, bitmapset)	Returns a merging of two sets
bitmapset_intersect(bitmapset, bitmapset)	Returns the intersection of two sets
bitmapset_difference(bitmapset, bitmapset)	Returns the set of elements of the first set with no elements in the second set
bitmapset_overlap(bitmapset, bitmapset)	Returns true if they intersect the set
bitmapset_is_empty(bitmapset)	Returns true if the set of nonempty
bitmapset_num_members(bitmapset)	Returns the number of elements set
bitmapset_collect(integer)	Aggregate function that returns a bitmapset of collected integer values from group

pavel=# select pst.bitmapset '{1,2,7}';
 bitmapset 
-----------
 {1,2,7}
(1 row)

pavel=# select pst.add_member(pst.bitmapset '{1,2,7}',44);
 add_member 
------------
 {1,2,7,44}
(1 row)

pavel=# select pst.is_member('{}', 22);
 is_member 
-----------
 f
(1 row)

pavel=# select pst.bitmapset_union('{1,2,3}','{3,4,5}');
 bitmapset_union 
-----------------
 {1,2,3,4,5}
(1 row)

Auxiliary functions

counter(rec, rowsnumber, showdata)

When the specified number of records display information on the number of rows. This function is filter. The data that gets sent as a parameter to the output. This still counts the number of records processed and at a certain number of prints debug messages.

pavel=# insert into omega2 select (x.xx).* 
           from (select pst.counter(omega,200000, true) xx 
                    from omega
                ) x;
NOTICE:  processed 200000 rows, current value is '(5,8)'
NOTICE:  processed 200000 rows, current value is '(5,8)'
NOTICE:  processed 400000 rows, current value is '(6,8)'
NOTICE:  processed 400000 rows, current value is '(6,8)'
NOTICE:  processed 600000 rows, current value is '(7,8)'
NOTICE:  processed 600000 rows, current value is '(7,8)'
NOTICE:  processed 800000 rows, current value is '(1,8)'
NOTICE:  processed 800000 rows, current value is '(1,8)'
NOTICE:  processed 1000000 rows, current value is '(5,8)'
NOTICE:  processed 1000000 rows, current value is '(5,8)'
INSERT 0 1000000