New Functions/Improvements in PostgreSQL 9.1

1. SQL function format(text, …):

 This function is similar to the C function sprintf; However only the following conversion specifications are recognized:

%s interpolates the corresponding argument as a string
%I escapes its argument as an SQL identifier
%L escapes its argument as an SQL literal 
%% outputs a literal %. 

A conversion can reference an explicit parameter position by preceding the conversion specifier with n$, where n is the argument position Some Examples are given below:

postgres=# select format('%1$s %3$s', 1, 2, 3);
 format 
--------
 1 3
(1 row)

postgres=# select format('Hello %s', 'World');
   format    
-------------
 Hello World
(1 row)
postgres=# select format('Hello %s %1$s %s', 'World', 'Hello again');
            format             
-------------------------------
 Hello World World Hello again
(1 row)

2. New string functions concat(), concat_ws(), left(), right(), and reverse()


(i). concat() function: 
 It Concatenate all arguments. NULL arguments are ignored. Example is given below:

postgres=# select concat('one');
 concat 
--------
 one
(1 row)

postgres=# select concat(1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
        concat        
----------------------
 123hellotf2010-03-09
(1 row)

(ii). concat_ws() function:
It works similar to concat() function, however in this function first argument will be seperators. Example is given below:

postgres=# select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','YYYYMMDD'));
         concat_ws          
----------------------------
 1#2#3#hello#t#f#2010-03-09
(1 row)

postgres=# select concat_ws(',',10,20,null,30);
 concat_ws 
-----------
 10,20,30
(1 row)

(iii). left() function: This function returns first n characters in the string. When n is negative, return all but last |n| characters.

postgres=# select left('Hello World',2);
 left 
------
 He
(1 row)
postgres=# select left('Hello World',-2);
   left    
-----------
 Hello Wor
(1 row)

(iv). right() function: This function returns last n characters in the string. When n is negative, return all but first |n| characters. Example is given below:

postgres=# select right('Hello World',2);
 right 
-------
 ld
(1 row)

postgres=# select right('Hello World',-2);
   right   
-----------
 llo World
(1 row)

(v) reverse() function:
   This function returns reversed string. Example is given below:
postgres=# select reverse('1234567');
 reverse 
---------
 7654321
(1 row)

3. pg_read_binary_file() function:
 This function is similar to pg_read_file, except it returns return content of Binary file in bytea format. Example is given below:

select pg_read_binary_file('testbin');
                              pg_read_binary_file                               
--------------------------------------------------------------------------------
 \x303131302020203030313109203031313020202030303031092030313131202020313030300a
(1 row)

4. New version of pg_read_file() for reading entire file:
 In Previous version of PostgreSQL, pg_read_file() requires offset and length of data needs to read. Now in PostgreSQL 9.1, there is new version of pg_read_file, using which user would be able to read entire file. Example is given below:

postgres=# select pg_read_file('postgresql.conf');
                                                 pg_read_file                                                  
---------------------------------------------------------------------------------------------------------------
 # -----------------------------                                                                              +
 # PostgreSQL configuration file                                                                              +
 # -----------------------------                                                                              +
 #                                                                                                            +
 # This file consists of lines of the form:                                                                   +
 #                                                                                                            +
 #   name = value                                                                                             +
 #                                                                                                            +
 # (The "=" is optional.)  Whitespace may be used.  Comments are introduced with                              +
 # "#" anywhere on a line.  The complete list of parameter names and allowed                                  +
 # values can be found in the PostgreSQL documentation.                                                       +
 #                                                                                                            +
 # The commented-out settings shown in this file represent the default values.                                +
 # Re-commenting a setting is NOT sufficient to revert it to the default value;                               +
 # you need to reload the server.                                                                             +
 #                                                                                                            +
 # This file is read on server startup and when the server receives a SIGHUP                                  +
 # signal.  If you edit the file on a running system, you have to SIGHUP the                                  +
 # server for the changes to take effect, or use "pg_ctl reload".  Some                                       +
 # parameters, which are marked below, require a server shutdown and restart to                               +
 # take effect.                                                                                               +
 #                                                                                                            +
 # Any parameter can also be given as a command-line option to the server, e.g.,                              +
 # "postgres -c log_connections=on".  Some parameters can be changed at run time                              +
 # with the "SET" SQL command.                                                                                +
 #                                                                                                            +
 # Memory units:  kB = kilobytes        Time units:  ms  = milliseconds                                       +
 #                MB = megabytes                     s   = seconds                                            +
 #                GB = gigabytes                     min = minutes                                            +
 #                                                   h   = hours                                              +
 #                                                   d   = days                                               +
                                                                                                              +
:

4. Optional Third argument in array_to_string()/string_to_array() functions for NULL processing.

 In PostgreSQL 9.1, array_to_string/string_to_array function has third argument for processing of NULL Values (In previous version of PostgreSQL, this was missing). Some Examples are given below:

postgres=# select array_to_string(array[1,2,3,4,NULL,6], ',', '*');
 array_to_string 
-----------------
 1,2,3,4,*,6
(1 row)

Similarly for string_to_array, example is given below:

postgres=# select string_to_array('1,2,3,4,*,6', ',', '*');
 string_to_array  
------------------
 {1,2,3,4,NULL,6}
(1 row)

XML Functions:

  1. XMLEXISTS and xpath_exists


(i). XMLEXISTS function:
 In postgreSQL XMLEXISTS Function has been added. Using this function, user can verify XPath's first argument returns any nodes or not. This function returns true or false. Example is given below

postgres=# SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol');
 xmlexists 
-----------
 f
(1 row)

postgres=# SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF 'Bidford-on-AvonCwmbranBristol');
 xmlexists 
-----------
 t

(ii) xpath_exists function: PostgreSQL 9.1, now has one more function xpath_exists. This function is a specialized form of the xpath function. Instead of returning the individual XML values that satisfy the XPath, this function returns a Boolean indicating whether the query was satisfied or not. This function is equivalent to the standard XMLEXISTS predicate, except that it also offers support for a namespace mapping argument. Example is given below:

postgres=# SELECT xpath_exists('//town[text() = ''Toronto'']','Bidford-on-AvonCwmbranBristol'::xml);
 xpath_exists 
--------------
 f
(1 row)
postgres=# SELECT xpath_exists('//town[text() = ''Cwmbran'']','Bidford-on-AvonCwmbranBristol'::xml);
 xpath_exists 
--------------
 t

2. xml_is_well_formed(), xml_is_well_formed_document(), xml_is_well_formed_content() functions:


(i) xml_is_well_formed() function: xml_is_well_formed_document checks for a well-formed document example is given below:

 postgres=# SELECT xml_is_well_formed('bar');
 xml_is_well_formed 
--------------------
 t
(1 row)

(ii). xml_is_well_formed_content() function: This function checks for xml well-formed content. Example is given below

SELECT xml_is_well_formed_content('bar');
 xml_is_well_formed_content
----------------------------
 t
(1 row)

SELECT xml_is_well_formed_content('abc');
 xml_is_well_formed_content
----------------------------
 t
(1 row)

(iii). xml_is_well_formed_document function: This function checks for a well-formed document. Example is given below:

postgres=# SELECT xml_is_well_formed_document('bar');
 xml_is_well_formed_document 
-----------------------------
 t
(1 row)

postgres=# SELECT xml_is_well_formed_document('bar');
 xml_is_well_formed_document 
-----------------------------
 f
(1 row)