CREATE EXTENSION hstore;
CREATE EXTENSION format;
-- Format specifiers can occur anywhere in the format string
SELECT format('%(name)s Hello', hstore('name', 'World'));
   format    
-------------
 World Hello
(1 row)

SELECT format('<%(name)s> Hello', hstore('name', 'World'));
    format     
---------------
 <World> Hello
(1 row)

SELECT format('Hello %(name)s', hstore('name', 'World'));
   format    
-------------
 Hello World
(1 row)

SELECT format('Hello <%(name)s>', hstore('name', 'World'));
    format     
---------------
 Hello <World>
(1 row)

SELECT format('A %(name)s B', hstore('name', 'World'));
  format   
-----------
 A World B
(1 row)

-- Format specifier with unmatched key
SELECT format('%(hi)s %(name)s', hstore('name', 'World'));
WARNING:  Key not found

 format 
--------
  World
(1 row)

-- Format specifier with matched key but NULL value
SELECT format('Hello %(name)s', hstore('name', NULL));
WARNING:  Null value

 format 
--------
 Hello 
(1 row)

-- Format specifier with punctuation in key name
SELECT format('Hello %(n@me)s', hstore('n@me', 'World'));
   format    
-------------
 Hello World
(1 row)

-- s, I, and L are type specifiers
SELECT format('%(name)s is %(type)s', hstore(ARRAY[
    'name', 'Melanie', 'type', 'cool']));
     format      
-----------------
 Melanie is cool
(1 row)

SELECT format('%(name)s is %(type)I', hstore(ARRAY[
    'name', 'Melanie', 'type', 'Cool']));
      format       
-------------------
 Melanie is "Cool"
(1 row)

SELECT format('%(name)s is %(type)L', hstore(ARRAY[
    'name', 'Melanie', 'type', 'cool']));
      format       
-------------------
 Melanie is 'cool'
(1 row)

SELECT format('%(name)I is %(type)I', hstore(ARRAY[
    'name', 'Melanie', 'type', 'Cool']));
       format        
---------------------
 "Melanie" is "Cool"
(1 row)

SELECT format('%(name)L is %(type)L', hstore(ARRAY[
    'name', 'Melanie', 'type', 'cool']));
       format        
---------------------
 'Melanie' is 'cool'
(1 row)

-- s, I, and L have special behavior on NULL values
SELECT format('%(null)s', hstore('null', NULL));
WARNING:  Null value

 format 
--------
 
(1 row)

SELECT format('%(null)I', hstore('null', NULL));
WARNING:  Null value

ERROR:  null values cannot be formatted as an SQL identifier
SELECT format('%(null)L', hstore('null', NULL));
WARNING:  Null value

 format 
--------
 'NULL'
(1 row)

-- Minimum width and alignment can be specified
SELECT format('%(name)-12L is %(type)L', hstore(ARRAY[
    'name', 'Melanie', 'type', 'cool']));
         format         
------------------------
 'Melanie'    is 'cool'
(1 row)

SELECT format('%(name)12L is %(type)L', hstore(ARRAY[
    'name', 'Melanie', 'type', 'cool']));
         format         
------------------------
    'Melanie' is 'cool'
(1 row)

SELECT format('%(name)-12L is %(type)8L', hstore(ARRAY[
    'name', 'Melanie', 'type', 'cool']));
          format          
--------------------------
 'Melanie'    is   'cool'
(1 row)