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