PostgreSQL QUOTE_IDENT Function

Summary: In this tutorial, you’ll learn how to quote an identifier using the PostgreSQL QUOTE_IDENT function.

Introduction to the PostgreSQL QUOTE_IDENT Function #

In PostgreSQL, an identifier is a name that represents database objects such as:

• Tables
• Columns
• Schemas
• Indexes
• Functions
• Procedures
• Views
• Sequences

The QUOTE_IDENT function safely quotes an identifier to ensure PostgreSQL correctly parses it. The QUOTE_IDENT function helps construct dynamic SQL queries appropriately to prevent SQL injection attacks.

Here’s the syntax of the QUOTE_IDENT function:

QUOTE_IDENT(identifier)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The QUOTE_IDENT takes an identifier you want to quote. It returns a correctly quoted identifier as a string.

Basic PostgreSQL QUOTE_IDENT Function examples #

Let’s take some examples of using QUOTE_IDENT function.

Quoting an Identifier #

The following example uses the QUOTE_IDENT function to quote an identifier:

SELECT QUOTE_IDENT('table');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 quote_ident
-------------
 "table"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since table is a reserved keyword, the QUOTE_IDENT quotes it appropriately.

Quoting a Regular Identifier #

The following statement uses the QUOTE_IDENT function to quote a regular identifier:

SELECT QUOTE_IDENT('products');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 quote_ident
-------------
 productsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since "products" does not contain reserved keywords or special characters, the QUOTE_IDENT function does not quote it.

Quoting an Identifier with Spaces #

The following example uses the QUOTE_IDENT function to quote a table name with a space:

SELECT QUOTE_IDENT('my table');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 quote_ident
-------------
 "my table"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The QUOTE_IDENT quotes the "my table" because it has a space.

Using PostgreSQL QUOTE_IDENT Function to Generate Secure SQL #

First, create a user-defined function called find_all, which retrieves data from specified columns of a table:

CREATE OR REPLACE FUNCTION find_all(
   from_table_name TEXT, 
   column_names TEXT[]
) 
RETURNS SETOF RECORD 
AS 
$$
DECLARE
    sql_query TEXT;
    safe_table_name TEXT;
    safe_column_list TEXT;
    valid_columns TEXT[];
BEGIN
    -- Validate the table name
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_name = from_table_name 
        AND table_schema = 'public'
    ) THEN
        RAISE EXCEPTION 'Table "%" does not exist', from_table_name;
    END IF;

    -- Get valid column names for the table
    SELECT array_agg(quote_ident(column_name))
    INTO valid_columns
    FROM information_schema.columns
    WHERE table_name = from_table_name 
    AND table_schema = 'public';

    -- Ensure all input columns exist in the table
    IF NOT column_names <@ valid_columns THEN
        RAISE EXCEPTION 'One or more invalid column names provided';
    END IF;

    -- Sanitize table and column names
    safe_table_name := quote_ident(from_table_name);
    safe_column_list := array_to_string(column_names, ', ');

    -- Construct the query
    sql_query := 'SELECT ' || safe_column_list || ' FROM ' || safe_table_name;

    -- Execute the query
    RETURN QUERY EXECUTE sql_query;
END;
$$ 
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How the function works:

• Step 1. Check the existence of the table to ensure it exists before querying.
• Step 2. Validate columns to ensure all are valid.
• Step 3. Prevent SQL injection using the QUOTE_IDENT function.
• Step 4. Construct and execute dynamic SQL.

Second, call the find_all function to get the data from product_id, product_name, and price columns of the products table:

SELECT
  *
FROM
  find_all (
    'products',
    ARRAY['product_id', 'product_name', 'price']
  ) AS t (
    product_id INT,
    product_name VARCHAR,
    price NUMERIC
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

• Use the QUOTE_IDENT function in dynamic SQL to safely quote an identifier.