quote_ident()

A function which formats identifiers for use in SQL strings

quote_ident() is a system function which formats an identifier for use in an SQL statement string.

quote_ident() was added in PostgreSQL 7.1.

Usage

quote_ident ( text ) → text

quote_ident() is commonly used in PL/pgSQL, along with quote_literal(), to generate dynamic queries using variables.

Change history

Examples

Basic usage:

postgres=# SELECT quote_ident('Foo "Bar"');
  quote_ident  
---------------
 "Foo ""Bar"""
(1 row)

Practical use case:

postgres=# CREATE TABLE "FOO"  (id SERIAL);
CREATE TABLE

postgres=# SELECT pg_get_serial_sequence(quote_ident('FOO'), 'id');
 pg_get_serial_sequence 
------------------------
 public."FOO_id_seq"
(1 row)

Usage in PL/pgSQL:

postgres=# CREATE OR REPLACE FUNCTION dyntest (
               tablename TEXT,
               colname TEXT
             )
             RETURNS INT
             LANGUAGE plpgsql STABLE
           AS $$
             DECLARE
               intval INT;
             BEGIN
               EXECUTE 'SELECT ' || quote_ident(colname) ||
                         'FROM ' || quote_ident(tablename) ||
                        'LIMIT 1'
                  INTO intval;
               RETURN intval;
             END;
           $$;

postgres=# INSERT INTO "FOO" values (1);
INSERT 0 1

postgres=# SELECT dyntest('FOO', 'ID');
 dyntest 
---------
       1
(1 row)

Categories

System function

See also

quote_literal(), parse_ident()