<application>PL/pgSQL</application> functions (and is not needed).
    </para>
   </note>

   <example id="plpgsql-quote-literal-example">
   <title>Quoting Values in Dynamic Queries</title>

    <indexterm>
     <primary>quote_ident</primary>
     <secondary>use in PL/pgSQL</secondary>
    </indexterm>

    <indexterm>
     <primary>quote_literal</primary>
     <secondary>use in PL/pgSQL</secondary>
    </indexterm>

    <indexterm>
     <primary>quote_nullable</primary>
     <secondary>use in PL/pgSQL</secondary>
    </indexterm>

    <indexterm>
     <primary>format</primary>
     <secondary>use in PL/pgSQL</secondary>
    </indexterm>

    <para>
     When working with dynamic commands you will often have to handle escaping
     of single quotes.  The recommended method for quoting fixed text in your
     function body is dollar quoting.  (If you have legacy code that does
     not use dollar quoting, please refer to the
     overview in <xref linkend="plpgsql-quote-tips"/>, which can save you
     some effort when translating said code to a more reasonable scheme.)
    </para>

    <para>
     Dynamic values require careful handling since they might contain
     quote characters.
     An example using <function>format()</function> (this assumes that you are
     dollar quoting the function body so quote marks need not be doubled):
<programlisting>
EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname) USING newvalue, keyvalue;
</programlisting>
     It is also possible to call the quoting functions directly:
<programlisting>
EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);
</programlisting>
    </para>

    <para>
     This example demonstrates the use of the
     <function>quote_ident</function> and
     <function>quote_literal</function> functions (see <xref
     linkend="functions-string"/>).  For safety, expressions containing column
     or table identifiers should be passed through
     <function>quote_ident</function> before insertion in a dynamic query.
     Expressions containing values that should be literal strings in the
     constructed command should be passed through <function>quote_literal</function>.
     These functions take the appropriate steps to return the input text
     enclosed in double or single quotes respectively, with any embedded
     special characters properly escaped.
    </para>

    <para>
     Because <function>quote_literal</function> is labeled
     <literal>STRICT</literal>, it will always return null when called with a
     null argument.  In the above example, if <literal>newvalue</literal> or
     <literal>keyvalue</literal> were null, the entire dynamic query string would
     become null, leading to an error from <command>EXECUTE</command>.
     You can avoid this problem by using the <function>quote_nullable</function>
     function, which works the same as <function>quote_literal</function> except that
     when called with a null argument it returns the string <literal>NULL</literal>.
     For example,
<programlisting>
EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);
</programlisting>
     If you are dealing with values that might be null, you should usually
     use <function>quote_nullable</function> in place of <function>quote_literal</function>.
    </para>

    <para>
     As always, care must be taken to ensure that null values in a query do
     not deliver unintended results.  For example the <literal>WHERE</literal> clause
<programlisting>
'WHERE key = ' || quote_nullable(keyvalue)
</programlisting>
     will never succeed if <literal>keyvalue</literal> is null, because the
     result of using the equality operator <literal>=</literal> with a null operand
     is always null.  If you wish null to work like