codekingpro/portable-devtools
115k
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>43.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="43.11. PL/pgSQL under the Hood" /><link rel="next" href="plpgsql-porting.html" title="43.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">43.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="43.11. PL/pgSQL under the Hood">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-porting.html" title="43.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">43.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">43.12.1. Handling of Quotation Marks</a></span></dt><dt><span class="sect2"><a href="plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS">43.12.2. Additional Compile-Time and Run-Time Checks</a></span></dt></dl></div><p>3 One good way to develop in4 <span class="application">PL/pgSQL</span> is to use the text editor of your5 choice to create your functions, and in another window, use6 <span class="application">psql</span> to load and test those functions.7 If you are doing it this way, it8 is a good idea to write the function using <code class="command">CREATE OR9 REPLACE FUNCTION</code>. That way you can just reload the file to update10 the function definition. For example:11</p><pre class="programlisting">12CREATE 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 such18 a function definition file with:19</p><pre class="programlisting">20\i filename.sql21</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 a25 GUI database access tool that facilitates development in a26 procedural language. One example of such a tool is27 <span class="application">pgAdmin</span>, although others exist. These tools often28 provide convenient features such as escaping single quotes and29 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">43.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 in32 <code class="command">CREATE FUNCTION</code> as a string literal. If you33 write the string literal in the ordinary way with surrounding34 single quotes, then any single quotes inside the function body35 must be doubled; likewise any backslashes must be doubled (assuming36 escape string syntax is used).37 Doubling quotes is at best tedious, and in more complicated cases38 the code can become downright incomprehensible, because you can39 easily find yourself needing half a dozen or more adjacent quote marks.40 It's recommended that you instead write the function body as a41 <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-quoting42 approach, you never double any quote marks, but instead take care to43 choose a different dollar-quoting delimiter for each level of44 nesting you need. For example, you might write the <code class="command">CREATE45 FUNCTION</code> command as:46</p><pre class="programlisting">47CREATE 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 in52 SQL commands and <code class="literal">$$</code> to delimit fragments of SQL commands53 that you are assembling as strings. If you need to quote text that54 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 quote57 marks without dollar quoting. It might be useful when translating58 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">62CREATE FUNCTION foo() RETURNS integer AS '63 ....64' LANGUAGE plpgsql;65</pre><p>66 Anywhere within a single-quoted function body, quote marks67 <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">71a_output := ''Blah'';72SELECT * FROM users WHERE f_name=''foobar'';73</pre><p>74 In the dollar-quoting approach, you'd just write:75</p><pre class="programlisting">76a_output := 'Blah';77SELECT * FROM users WHERE f_name='foobar';78</pre><p>79 which is exactly what the <span class="application">PL/pgSQL</span> parser would see80 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 the83 function body, for example:84</p><pre class="programlisting">85a_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">92a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$93</pre><p>94 being careful that any dollar-quote delimiters around this are not95 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 is98 adjacent to the end of that string constant, for example:99</p><pre class="programlisting">100a_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">107a_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 (which111 accounts for 8 quotation marks) and this is adjacent to the end of that112 string constant (2 more). You will probably only need that if113 you are writing a function that generates other functions, as in114 <a class="xref" href="plpgsql-porting.html#PLPGSQL-PORTING-EX2" title="Example 43.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example 43.10</a>.115 For example:116</p><pre class="programlisting">117a_output := a_output || '' if v_'' ||118 referrer_keys.kind || '' like ''''''''''119 || referrer_keys.key_string || ''''''''''120 then return '''''' || referrer_keys.referrer_type121 || ''''''; end if;'';122</pre><p>123 The value of <code class="literal">a_output</code> would then be:124</p><pre class="programlisting">125if 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">130a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$131 || referrer_keys.key_string || $$'132 then return '$$ || referrer_keys.referrer_type133 || $$'; end if;$$;134</pre><p>135 where we assume we only need to put single quote marks into136 <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">43.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 before139 they cause harm, <span class="application">PL/pgSQL</span> provides additional140 <em class="replaceable"><code>checks</code></em>. When enabled, depending on the configuration, they141 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 received143 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>, or147 <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 variables151 <code class="varname">plpgsql.extra_warnings</code> for warnings and152 <code class="varname">plpgsql.extra_errors</code> for errors. Both can be set either to153 a comma-separated list of checks, <code class="literal">"none"</code> or154 <code class="literal">"all"</code>. The default is <code class="literal">"none"</code>. Currently155 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 assigning160 values to more than one variable at a time, such as161 <code class="command">SELECT INTO</code>. Typically, the number of target162 variables and the number of source variables should match, though163 <span class="application">PL/pgSQL</span> will use <code class="literal">NULL</code>164 for missing values and extra variables are ignored. Enabling this165 check will cause <span class="application">PL/pgSQL</span> to throw a166 <code class="literal">WARNING</code> or <code class="literal">ERROR</code> whenever the167 number of target variables and the number of source variables are168 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> to171 check if a given query returns more than one row when an172 <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 multiple174 rows is generally either inefficient and/or nondeterministic and175 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">181SET plpgsql.extra_warnings TO 'shadowed_variables';182 183CREATE FUNCTION foo(f1 int) RETURNS int AS $$184DECLARE185f1 int;186BEGIN187RETURN f1;188END;189$$ LANGUAGE plpgsql;190WARNING: variable "f1" shadows a previously defined variable191LINE 3: f1 int;192 ^193CREATE FUNCTION194</pre><p>195 The below example shows the effects of setting196 <code class="varname">plpgsql.extra_warnings</code> to197 <code class="varname">strict_multi_assignment</code>:198</p><pre class="programlisting">199SET plpgsql.extra_warnings TO 'strict_multi_assignment';200 201CREATE OR REPLACE FUNCTION public.foo()202 RETURNS void203 LANGUAGE plpgsql204AS $$205DECLARE206 x int;207 y int;208BEGIN209 SELECT 1 INTO x, y;210 SELECT 1, 2 INTO x, y;211 SELECT 1, 2, 3 INTO x, y;212END;213$$;214 215SELECT foo();216WARNING: number of source and target fields in assignment does not match217DETAIL: strict_multi_assignment check of extra_warnings is active.218HINT: Make sure the query returns the exact list of columns.219WARNING: number of source and target fields in assignment does not match220DETAIL: strict_multi_assignment check of extra_warnings is active.221HINT: Make sure the query returns the exact list of columns.222 223 foo224-----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="43.11. PL/pgSQL under the Hood">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-porting.html" title="43.13. Porting from Oracle PL/SQL">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.11. <span class="application">PL/pgSQL</span> under the Hood </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.13. Porting from <span class="productname">Oracle</span> PL/SQL</td></tr></table></div></body></html>