PostgreSQL FORMAT Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL FORMAT() function to construct formatted messages and dynamic SQL statements.

Introduction to the PostgreSQL FORMAT function #

The FORMAT() function return a formatted string based a template.

Here’s the syntax of the FORMAT() function:

FORMAT(format_string, format_argument1, format_argument2, ...)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The FORMAT() function accepts a variable number of parameters:

• format_string: This is the string that includes format specifiers.
• format_argument1, format_argument2, …: the arguments you use to in the format_string.

The format_string may include one or more format specifiers:

• %s formats the argument as a string.
• %I formats the argument as an SQL identifier, such as a table name with proper quoting.
• %L formats the argument as an SQL literal.

To include the percent sign (%), you can use two percent sign %%. The first one escapes the second.

The format specifiers %I and %L can be handy for constructing dynamic SQL statements.

Basic PostgreSQL FORMAT function examples #

The following SELECT statement uses the FORMAT function to return a formatted string:

SELECT
  FORMAT('Hello, %s', 'pgtutorial.com');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        format
-----------------------
 Hello, pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the FORMAT function replaces the format specifier %s by the string argument 'pgtutorial.com'.

Constructing dynamic SQL statements #

The following example uses the FORMAT function with the %I specifiers to return a dynamic SQL statement:

SELECT
  FORMAT(
    'SELECT %I, %I FROM %I',
    'product_name',
    'price',
    'products'
  ) sql_statement;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

              sql_statement
------------------------------------------
 SELECT product_name, price FROM productsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the FORMAT function with the %L for formatting literals:

SELECT
  FORMAT(
    'SELECT %I, %I FROM %I WHERE %I = %L',
    'product_name',
    'price',
    'products',
    'product_name',
    'Apple iPhone 15'
  ) sql_statement;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

                                  sql_statement
---------------------------------------------------------------------------------
 SELECT product_name, price FROM products WHERE product_name = 'Apple iPhone 15'Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The %L format specifier quotes the literal string correctly.

Practical examples of the FORMAT function #

First, create a PL/pgSQL function that uses the FORMAT() function to select all columns from the products table and sort the result set based on a column in an order specified by parameters:

CREATE OR REPLACE FUNCTION get_products_sorted (
  p_sort_column TEXT DEFAULT 'product_id',
  p_sort_order TEXT DEFAULT 'ASC'
) RETURNS SETOF products AS $$
DECLARE
    sql_command TEXT;
    sort_order_clean TEXT;
BEGIN
    IF UPPER(p_sort_order) = 'DESC' THEN
        sort_order_clean := 'DESC';
    ELSE
        sort_order_clean := 'ASC';
    END IF;

    -- Construct the dynamic SELECT statement.
    sql_command := format('SELECT * FROM products ORDER BY %I %s;',
                          p_sort_column,
                          sort_order_clean);

    -- Optionally, log the dynamic SQL for debugging purposes.
    RAISE NOTICE 'Executing SQL: %', sql_command;

    -- Execute the dynamic SQL and return the result.
    RETURN QUERY EXECUTE sql_command;
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, retrieve the product name and price, and sort the products by the price from high to low by calling the get_products_sorted function:

SELECT
  product_name,
  price
FROM
  get_products_sorted ('price', 'DESC');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        |  price
----------------------------+---------
 Samsung QN900C Neo QLED    | 2999.99
 LG G3 OLED                 | 2499.99
 Sony Bravia XR A95K        | 2499.99
 LG OLED TV C3              | 1999.99
 Samsung Galaxy Z Fold 5    | 1799.99
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the FORMAT function to construct a formatted string or a dynamic SQL statement.