quote_literal()

A function which formats arbitrary input as an SQL string literal

quote_literal() is a system function which formats arbitrary input as an appropriately quoted and escaped SQL string literal.

quote_literal() was added in PostgreSQL 7.1.

Usage

quote_literal ( anyelement ) → text

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

Change history

Examples

Basic usage example for quote_literal():

postgres=# SELECT quote_literal(1.2), quote_literal(array[1,2]);
 quote_literal | quote_literal 
---------------+---------------
 '1.2'         | '{1,2}'
(1 row)

Usage in PL/pgSQL:

postgres=# CREATE TABLE foo (val TEXT);
CREATE TABLE

postgres=# CREATE OR REPLACE FUNCTION littest (
               someval TEXT
             )
             RETURNS VOID
             LANGUAGE plpgsql
           AS $$
             BEGIN
               EXECUTE 'INSERT INTO foo VALUES (' || quote_literal(someval) || ')';
             END;
           $$;
CREATE FUNCTION

postgres=# SELECT littest('bar');
 littest 
---------
 
(1 row)

postgres=# SELECT * FROM foo;
 val 
-----
 bar
(1 row)

Categories

System function

See also

quote_ident()