CREATE EXTENSION IF NOT EXISTS format_x;
NOTICE:  extension "format_x" already exists, skipping
-- JSONB --
SELECT format_x('Hello %(name)s',
  '{"name": "United States", "code": "US", "population": 1000}'::JSONB);
      format_x       
---------------------
 Hello United States
(1 row)

SELECT format_x('Hello %(name)s <%(code)s>',
  '{"name": "United States", "code": "US", "population": 1000}'::JSONB);
         format_x         
--------------------------
 Hello United States <US>
(1 row)

SELECT format_x('%(name)s: %(population)s',
  '{"name": "United States", "code": "US", "population": 1000}'::JSONB);
      format_x       
---------------------
 United States: 1000
(1 row)

SELECT format_x('%2(name)s %1(name)s',
  '{"name": "United States", "code": "US", "population": 1000}'::JSONB,
  '{"name": "Canada", "code": "CA", "population": 30}'::JSONB
);
       format_x       
----------------------
 Canada United States
(1 row)

SELECT format_x('%2(name)s %1(name)s', VARIADIC ARRAY[
  '{"name": "United States", "code": "US", "population": 1000}'::JSONB,
  '{"name": "Canada", "code": "CA", "population": 30}'::JSONB
]);
       format_x       
----------------------
 Canada United States
(1 row)

SELECT format_x('SELECT * FROM nation WHERE code = %(code)I',
  '{"name": "United States", "code": "US", "population": 1000}'::JSONB);
                format_x                
----------------------------------------
 SELECT * FROM nation WHERE code = "US"
(1 row)

SELECT format_x('SELECT * FROM nation WHERE code = %(code)L',
  '{"name": "United States", "code": "US", "population": 1000}'::JSONB);
                format_x                
----------------------------------------
 SELECT * FROM nation WHERE code = 'US'
(1 row)

-- JSONB with missing key --
SELECT format_x('%(size)s', '{"name": "United Kingdom"}'::JSONB);
ERROR:  key "size" does not exist
-- JSONB with null value --
SELECT format_x('%(name)s %(code)I %(population)L',
  '{"name": "United Kingdom", "code": "UK", "population": 200}'::JSONB);
         format_x          
---------------------------
 United Kingdom "UK" '200'
(1 row)

SELECT format_x('%(name)s %(code)I %(population)L',
  '{"name": null, "code": "UK", "population": 200}'::JSONB);
  format_x   
-------------
  "UK" '200'
(1 row)

SELECT format_x('%(name)s %(code)I %(population)L',
  '{"name": "United Kingdom", "code": null, "population": 200}'::JSONB);
ERROR:  null values cannot be formatted as an SQL identifier
SELECT format_x('%(name)s %(code)I %(population)L',
  '{"name": "United Kingdom", "code": "UK", "population": null}'::JSONB);
         format_x         
--------------------------
 United Kingdom "UK" NULL
(1 row)

-- JSONB with nested object --
SELECT format_x('%(n1.code)I, %(n2.name)s', '{
  "n1": {"name": "United States", "code": "US", "population": 1000},
  "n2": {"name": "Canada", "code": "CA", "population": 30}
}'::JSONB);
   format_x   
--------------
 "US", Canada
(1 row)

SELECT format_x('%(n1.code)I, %(n2.name)s', '{
  "n1": {"name": "United States", "code": "US", "population": 1000},
  "n2": null
}'::JSONB);
ERROR:  null arguments cannot be looked up in, so cannot be passed for named parameters
SELECT format_x('%(n1.code)I, %(n2)s', '{
  "n1": {"name": "United States", "code": "US", "population": 1000},
  "n2": {"name": "Canada", "code": "CA", "population": 30}
}'::JSONB);
                         format_x                         
----------------------------------------------------------
 "US", {"code": "CA", "name": "Canada", "population": 30}
(1 row)

SELECT format_x('%(n1.code)I, %(n2)L', '{
  "n1": {"name": "United States", "code": "US", "population": 1000},
  "n2": {"name": "Canada", "code": "CA", "population": 30}
}'::JSONB);
                          format_x                          
------------------------------------------------------------
 "US", '{"code": "CA", "name": "Canada", "population": 30}'
(1 row)

-- Composite type with nested JSONB attribute --
CREATE TABLE description(name TEXT, data JSONB);
INSERT INTO description VALUES
  ('United States', '{"code": "US", "population": 1000}'::JSONB);
SELECT format_x('%(name)I: %(data.population)s', description)
  FROM description;
       format_x        
-----------------------
 "United States": 1000
(1 row)

DROP TABLE description;