begriffs open source - ai-pg/blob - full-docs/src/sgml/html/plpgsql-development-tips.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>41.12. Tips for Developing in PL/pgSQL</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-implementation.html" title="41.11. PL/pgSQL under the Hood" /><link rel="next" href="plpgsql-porting.html" title="41.13. Porting from Oracle PL/SQL" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">41.12. Tips for Developing in <span class="application">PL/pgSQL</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-implementation.html" title="41.11. PL/pgSQL under the Hood">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 41. <span class="application">PL/pgSQL</span> — <acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-porting.html" title="41.13. Porting from Oracle PL/SQL">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-DEVELOPMENT-TIPS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">41.12. Tips for Developing in <span class="application">PL/pgSQL</span> <a href="#PLPGSQL-DEVELOPMENT-TIPS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS">41.12.1. Handling of Quotation Marks</a></span></dt><dt><span class="sect2"><a href="plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS">41.12.2. Additional Compile-Time and Run-Time Checks</a></span></dt></dl></div><p>
   3     One good way to develop in
   4     <span class="application">PL/pgSQL</span> is to use the text editor of your
   5     choice to create your functions, and in another window, use
   6     <span class="application">psql</span> to load and test those functions.
   7     If you are doing it this way, it
   8     is a good idea to write the function using <code class="command">CREATE OR
   9     REPLACE FUNCTION</code>. That way you can just reload the file to update
  10     the function definition.  For example:
  11 </p><pre class="programlisting">
  12 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
  13           ....
  14 $$ LANGUAGE plpgsql;
  15 </pre><p>
  16    </p><p>
  17     While running <span class="application">psql</span>, you can load or reload such
  18     a function definition file with:
  19 </p><pre class="programlisting">
  20 \i filename.sql
  21 </pre><p>
  22     and then immediately issue SQL commands to test the function.
  23    </p><p>
  24     Another good way to develop in <span class="application">PL/pgSQL</span> is with a
  25     GUI database access tool that facilitates development in a
  26     procedural language. One example of such a tool is
  27     <span class="application">pgAdmin</span>, although others exist. These tools often
  28     provide convenient features such as escaping single quotes and
  29     making it easier to recreate and debug functions.
  30    </p><div class="sect2" id="PLPGSQL-QUOTE-TIPS"><div class="titlepage"><div><div><h3 class="title">41.12.1. Handling of Quotation Marks <a href="#PLPGSQL-QUOTE-TIPS" class="id_link">#</a></h3></div></div></div><p>
  31     The code of a <span class="application">PL/pgSQL</span> function is specified in
  32     <code class="command">CREATE FUNCTION</code> as a string literal.  If you
  33     write the string literal in the ordinary way with surrounding
  34     single quotes, then any single quotes inside the function body
  35     must be doubled; likewise any backslashes must be doubled (assuming
  36     escape string syntax is used).
  37     Doubling quotes is at best tedious, and in more complicated cases
  38     the code can become downright incomprehensible, because you can
  39     easily find yourself needing half a dozen or more adjacent quote marks.
  40     It's recommended that you instead write the function body as a
  41     <span class="quote">“<span class="quote">dollar-quoted</span>”</span> string literal (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING" title="4.1.2.4. Dollar-Quoted String Constants">Section 4.1.2.4</a>).  In the dollar-quoting
  42     approach, you never double any quote marks, but instead take care to
  43     choose a different dollar-quoting delimiter for each level of
  44     nesting you need.  For example, you might write the <code class="command">CREATE
  45     FUNCTION</code> command as:
  46 </p><pre class="programlisting">
  47 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
  48           ....
  49 $PROC$ LANGUAGE plpgsql;
  50 </pre><p>
  51     Within this, you might use quote marks for simple literal strings in
  52     SQL commands and <code class="literal">$$</code> to delimit fragments of SQL commands
  53     that you are assembling as strings.  If you need to quote text that
  54     includes <code class="literal">$$</code>, you could use <code class="literal">$Q$</code>, and so on.
  55    </p><p>
  56     The following chart shows what you have to do when writing quote
  57     marks without dollar quoting.  It might be useful when translating
  58     pre-dollar quoting code into something more comprehensible.
  59   </p><div class="variablelist"><dl class="variablelist"><dt id="PLPGSQL-QUOTE-TIPS-1-QUOT"><span class="term">1 quotation mark</span> <a href="#PLPGSQL-QUOTE-TIPS-1-QUOT" class="id_link">#</a></dt><dd><p>
  60       To begin and end the function body, for example:
  61 </p><pre class="programlisting">
  62 CREATE FUNCTION foo() RETURNS integer AS '
  63           ....
  64 ' LANGUAGE plpgsql;
  65 </pre><p>
  66       Anywhere within a single-quoted function body, quote marks
  67       <span class="emphasis"><em>must</em></span> appear in pairs.
  68      </p></dd><dt id="PLPGSQL-QUOTE-TIPS-2-QUOT"><span class="term">2 quotation marks</span> <a href="#PLPGSQL-QUOTE-TIPS-2-QUOT" class="id_link">#</a></dt><dd><p>
  69       For string literals inside the function body, for example:
  70 </p><pre class="programlisting">
  71 a_output := ''Blah'';
  72 SELECT * FROM users WHERE f_name=''foobar'';
  73 </pre><p>
  74       In the dollar-quoting approach, you'd just write:
  75 </p><pre class="programlisting">
  76 a_output := 'Blah';
  77 SELECT * FROM users WHERE f_name='foobar';
  78 </pre><p>
  79       which is exactly what the <span class="application">PL/pgSQL</span> parser would see
  80       in either case.
  81      </p></dd><dt id="PLPGSQL-QUOTE-TIPS-4-QUOT"><span class="term">4 quotation marks</span> <a href="#PLPGSQL-QUOTE-TIPS-4-QUOT" class="id_link">#</a></dt><dd><p>
  82       When you need a single quotation mark in a string constant inside the
  83       function body, for example:
  84 </p><pre class="programlisting">
  85 a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
  86 </pre><p>
  87       The value actually appended to <code class="literal">a_output</code> would be:
  88       <code class="literal"> AND name LIKE 'foobar' AND xyz</code>.
  89      </p><p>
  90       In the dollar-quoting approach, you'd write:
  91 </p><pre class="programlisting">
  92 a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
  93 </pre><p>
  94       being careful that any dollar-quote delimiters around this are not
  95       just <code class="literal">$$</code>.
  96      </p></dd><dt id="PLPGSQL-QUOTE-TIPS-6-QUOT"><span class="term">6 quotation marks</span> <a href="#PLPGSQL-QUOTE-TIPS-6-QUOT" class="id_link">#</a></dt><dd><p>
  97       When a single quotation mark in a string inside the function body is
  98       adjacent to the end of that string constant, for example:
  99 </p><pre class="programlisting">
 100 a_output := a_output || '' AND name LIKE ''''foobar''''''
 101 </pre><p>
 102       The value appended to <code class="literal">a_output</code> would then be:
 103       <code class="literal"> AND name LIKE 'foobar'</code>.
 104      </p><p>
 105       In the dollar-quoting approach, this becomes:
 106 </p><pre class="programlisting">
 107 a_output := a_output || $$ AND name LIKE 'foobar'$$
 108 </pre><p>
 109      </p></dd><dt id="PLPGSQL-QUOTE-TIPS-10-QUOT"><span class="term">10 quotation marks</span> <a href="#PLPGSQL-QUOTE-TIPS-10-QUOT" class="id_link">#</a></dt><dd><p>
 110       When you want two single quotation marks in a string constant (which
 111       accounts for 8 quotation marks) and this is adjacent to the end of that
 112       string constant (2 more).  You will probably only need that if
 113       you are writing a function that generates other functions, as in
 114       <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX2" title="Example 41.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example 41.10</a>.
 115       For example:
 116 </p><pre class="programlisting">
 117 a_output := a_output || '' if v_'' ||
 118     referrer_keys.kind || '' like ''''''''''
 119     || referrer_keys.key_string || ''''''''''
 120     then return ''''''  || referrer_keys.referrer_type
 121     || ''''''; end if;'';
 122 </pre><p>
 123       The value of <code class="literal">a_output</code> would then be:
 124 </p><pre class="programlisting">
 125 if v_... like ''...'' then return ''...''; end if;
 126 </pre><p>
 127      </p><p>
 128       In the dollar-quoting approach, this becomes:
 129 </p><pre class="programlisting">
 130 a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
 131     || referrer_keys.key_string || $$'
 132     then return '$$  || referrer_keys.referrer_type
 133     || $$'; end if;$$;
 134 </pre><p>
 135       where we assume we only need to put single quote marks into
 136       <code class="literal">a_output</code>, because it will be re-quoted before use.
 137      </p></dd></dl></div></div><div class="sect2" id="PLPGSQL-EXTRA-CHECKS"><div class="titlepage"><div><div><h3 class="title">41.12.2. Additional Compile-Time and Run-Time Checks <a href="#PLPGSQL-EXTRA-CHECKS" class="id_link">#</a></h3></div></div></div><p>
 138     To aid the user in finding instances of simple but common problems before
 139     they cause harm, <span class="application">PL/pgSQL</span> provides additional
 140     <em class="replaceable"><code>checks</code></em>. When enabled, depending on the configuration, they
 141     can be used to emit either a <code class="literal">WARNING</code> or an <code class="literal">ERROR</code>
 142     during the compilation of a function. A function which has received
 143     a <code class="literal">WARNING</code> can be executed without producing further messages,
 144     so you are advised to test in a separate development environment.
 145    </p><p>
 146     Setting <code class="varname">plpgsql.extra_warnings</code>, or
 147     <code class="varname">plpgsql.extra_errors</code>, as appropriate, to <code class="literal">"all"</code>
 148     is encouraged in development and/or testing environments.
 149    </p><p>
 150     These additional checks are enabled through the configuration variables
 151     <code class="varname">plpgsql.extra_warnings</code> for warnings and
 152     <code class="varname">plpgsql.extra_errors</code> for errors. Both can be set either to
 153     a comma-separated list of checks, <code class="literal">"none"</code> or
 154     <code class="literal">"all"</code>. The default is <code class="literal">"none"</code>. Currently
 155     the list of available checks includes:
 156     </p><div class="variablelist"><dl class="variablelist"><dt id="PLPGSQL-EXTRA-CHECKS-SHADOWED-VARIABLES"><span class="term"><code class="varname">shadowed_variables</code></span> <a href="#PLPGSQL-EXTRA-CHECKS-SHADOWED-VARIABLES" class="id_link">#</a></dt><dd><p>
 157         Checks if a declaration shadows a previously defined variable.
 158        </p></dd><dt id="PLPGSQL-EXTRA-CHECKS-STRICT-MULTI-ASSIGNMENT"><span class="term"><code class="varname">strict_multi_assignment</code></span> <a href="#PLPGSQL-EXTRA-CHECKS-STRICT-MULTI-ASSIGNMENT" class="id_link">#</a></dt><dd><p>
 159         Some <span class="application">PL/pgSQL</span> commands allow assigning
 160         values to more than one variable at a time, such as
 161         <code class="command">SELECT INTO</code>.  Typically, the number of target
 162         variables and the number of source variables should match, though
 163         <span class="application">PL/pgSQL</span> will use <code class="literal">NULL</code>
 164         for missing values and extra variables are ignored.  Enabling this
 165         check will cause <span class="application">PL/pgSQL</span> to throw a
 166         <code class="literal">WARNING</code> or <code class="literal">ERROR</code> whenever the
 167         number of target variables and the number of source variables are
 168         different.
 169        </p></dd><dt id="PLPGSQL-EXTRA-CHECKS-TOO-MANY-ROWS"><span class="term"><code class="varname">too_many_rows</code></span> <a href="#PLPGSQL-EXTRA-CHECKS-TOO-MANY-ROWS" class="id_link">#</a></dt><dd><p>
 170         Enabling this check will cause <span class="application">PL/pgSQL</span> to
 171         check if a given query returns more than one row when an
 172         <code class="literal">INTO</code> clause is used.  As an <code class="literal">INTO</code>
 173         statement will only ever use one row, having a query return multiple
 174         rows is generally either inefficient and/or nondeterministic and
 175         therefore is likely an error.
 176        </p></dd></dl></div><p>
 177
 178     The following example shows the effect of <code class="varname">plpgsql.extra_warnings</code>
 179     set to <code class="varname">shadowed_variables</code>:
 180 </p><pre class="programlisting">
 181 SET plpgsql.extra_warnings TO 'shadowed_variables';
 182
 183 CREATE FUNCTION foo(f1 int) RETURNS int AS $$
 184 DECLARE
 185 f1 int;
 186 BEGIN
 187 RETURN f1;
 188 END;
 189 $$ LANGUAGE plpgsql;
 190 WARNING:  variable "f1" shadows a previously defined variable
 191 LINE 3: f1 int;
 192         ^
 193 CREATE FUNCTION
 194 </pre><p>
 195     The below example shows the effects of setting
 196     <code class="varname">plpgsql.extra_warnings</code> to
 197     <code class="varname">strict_multi_assignment</code>:
 198 </p><pre class="programlisting">
 199 SET plpgsql.extra_warnings TO 'strict_multi_assignment';
 200
 201 CREATE OR REPLACE FUNCTION public.foo()
 202  RETURNS void
 203  LANGUAGE plpgsql
 204 AS $$
 205 DECLARE
 206   x int;
 207   y int;
 208 BEGIN
 209   SELECT 1 INTO x, y;
 210   SELECT 1, 2 INTO x, y;
 211   SELECT 1, 2, 3 INTO x, y;
 212 END;
 213 $$;
 214
 215 SELECT foo();
 216 WARNING:  number of source and target fields in assignment does not match
 217 DETAIL:  strict_multi_assignment check of extra_warnings is active.
 218 HINT:  Make sure the query returns the exact list of columns.
 219 WARNING:  number of source and target fields in assignment does not match
 220 DETAIL:  strict_multi_assignment check of extra_warnings is active.
 221 HINT:  Make sure the query returns the exact list of columns.
 222
 223  foo
 224 -----
 225
 226 (1 row)
 227 </pre><p>
 228    </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-implementation.html" title="41.11. PL/pgSQL under the Hood">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-porting.html" title="41.13. Porting from Oracle PL/SQL">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.11. <span class="application">PL/pgSQL</span> under the Hood </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.13. Porting from <span class="productname">Oracle</span> PL/SQL</td></tr></table></div></body></html>