diff options
Diffstat (limited to 'doc/src/sgml/html/plpgsql-development-tips.html')
| -rw-r--r-- | doc/src/sgml/html/plpgsql-development-tips.html | 228 |
1 files changed, 228 insertions, 0 deletions
diff --git a/doc/src/sgml/html/plpgsql-development-tips.html b/doc/src/sgml/html/plpgsql-development-tips.html new file mode 100644 index 0000000..b549182 --- /dev/null +++ b/doc/src/sgml/html/plpgsql-development-tips.html @@ -0,0 +1,228 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.12. Tips for Developing in <span xmlns="http://www.w3.org/1999/xhtml" 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 xmlns="http://www.w3.org/1999/xhtml" class="application">PL/pgSQL</span> — <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 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></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></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> + One good way to develop in + <span class="application">PL/pgSQL</span> is to use the text editor of your + choice to create your functions, and in another window, use + <span class="application">psql</span> to load and test those functions. + If you are doing it this way, it + is a good idea to write the function using <code class="command">CREATE OR + REPLACE FUNCTION</code>. That way you can just reload the file to update + the function definition. For example: +</p><pre class="programlisting"> +CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$ + .... +$$ LANGUAGE plpgsql; +</pre><p> + </p><p> + While running <span class="application">psql</span>, you can load or reload such + a function definition file with: +</p><pre class="programlisting"> +\i filename.sql +</pre><p> + and then immediately issue SQL commands to test the function. + </p><p> + Another good way to develop in <span class="application">PL/pgSQL</span> is with a + GUI database access tool that facilitates development in a + procedural language. One example of such a tool is + <span class="application">pgAdmin</span>, although others exist. These tools often + provide convenient features such as escaping single quotes and + making it easier to recreate and debug functions. + </p><div class="sect2" id="PLPGSQL-QUOTE-TIPS"><div class="titlepage"><div><div><h3 class="title">43.12.1. Handling of Quotation Marks</h3></div></div></div><p> + The code of a <span class="application">PL/pgSQL</span> function is specified in + <code class="command">CREATE FUNCTION</code> as a string literal. If you + write the string literal in the ordinary way with surrounding + single quotes, then any single quotes inside the function body + must be doubled; likewise any backslashes must be doubled (assuming + escape string syntax is used). + Doubling quotes is at best tedious, and in more complicated cases + the code can become downright incomprehensible, because you can + easily find yourself needing half a dozen or more adjacent quote marks. + It's recommended that you instead write the function body as a + <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 + approach, you never double any quote marks, but instead take care to + choose a different dollar-quoting delimiter for each level of + nesting you need. For example, you might write the <code class="command">CREATE + FUNCTION</code> command as: +</p><pre class="programlisting"> +CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$ + .... +$PROC$ LANGUAGE plpgsql; +</pre><p> + Within this, you might use quote marks for simple literal strings in + SQL commands and <code class="literal">$$</code> to delimit fragments of SQL commands + that you are assembling as strings. If you need to quote text that + includes <code class="literal">$$</code>, you could use <code class="literal">$Q$</code>, and so on. + </p><p> + The following chart shows what you have to do when writing quote + marks without dollar quoting. It might be useful when translating + pre-dollar quoting code into something more comprehensible. + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">1 quotation mark</span></dt><dd><p> + To begin and end the function body, for example: +</p><pre class="programlisting"> +CREATE FUNCTION foo() RETURNS integer AS ' + .... +' LANGUAGE plpgsql; +</pre><p> + Anywhere within a single-quoted function body, quote marks + <span class="emphasis"><em>must</em></span> appear in pairs. + </p></dd><dt><span class="term">2 quotation marks</span></dt><dd><p> + For string literals inside the function body, for example: +</p><pre class="programlisting"> +a_output := ''Blah''; +SELECT * FROM users WHERE f_name=''foobar''; +</pre><p> + In the dollar-quoting approach, you'd just write: +</p><pre class="programlisting"> +a_output := 'Blah'; +SELECT * FROM users WHERE f_name='foobar'; +</pre><p> + which is exactly what the <span class="application">PL/pgSQL</span> parser would see + in either case. + </p></dd><dt><span class="term">4 quotation marks</span></dt><dd><p> + When you need a single quotation mark in a string constant inside the + function body, for example: +</p><pre class="programlisting"> +a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz'' +</pre><p> + The value actually appended to <code class="literal">a_output</code> would be: + <code class="literal"> AND name LIKE 'foobar' AND xyz</code>. + </p><p> + In the dollar-quoting approach, you'd write: +</p><pre class="programlisting"> +a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$ +</pre><p> + being careful that any dollar-quote delimiters around this are not + just <code class="literal">$$</code>. + </p></dd><dt><span class="term">6 quotation marks</span></dt><dd><p> + When a single quotation mark in a string inside the function body is + adjacent to the end of that string constant, for example: +</p><pre class="programlisting"> +a_output := a_output || '' AND name LIKE ''''foobar'''''' +</pre><p> + The value appended to <code class="literal">a_output</code> would then be: + <code class="literal"> AND name LIKE 'foobar'</code>. + </p><p> + In the dollar-quoting approach, this becomes: +</p><pre class="programlisting"> +a_output := a_output || $$ AND name LIKE 'foobar'$$ +</pre><p> + </p></dd><dt><span class="term">10 quotation marks</span></dt><dd><p> + When you want two single quotation marks in a string constant (which + accounts for 8 quotation marks) and this is adjacent to the end of that + string constant (2 more). You will probably only need that if + you are writing a function that generates other functions, as in + <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>. + For example: +</p><pre class="programlisting"> +a_output := a_output || '' if v_'' || + referrer_keys.kind || '' like '''''''''' + || referrer_keys.key_string || '''''''''' + then return '''''' || referrer_keys.referrer_type + || ''''''; end if;''; +</pre><p> + The value of <code class="literal">a_output</code> would then be: +</p><pre class="programlisting"> +if v_... like ''...'' then return ''...''; end if; +</pre><p> + </p><p> + In the dollar-quoting approach, this becomes: +</p><pre class="programlisting"> +a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ + || referrer_keys.key_string || $$' + then return '$$ || referrer_keys.referrer_type + || $$'; end if;$$; +</pre><p> + where we assume we only need to put single quote marks into + <code class="literal">a_output</code>, because it will be re-quoted before use. + </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</h3></div></div></div><p> + To aid the user in finding instances of simple but common problems before + they cause harm, <span class="application">PL/pgSQL</span> provides additional + <em class="replaceable"><code>checks</code></em>. When enabled, depending on the configuration, they + can be used to emit either a <code class="literal">WARNING</code> or an <code class="literal">ERROR</code> + during the compilation of a function. A function which has received + a <code class="literal">WARNING</code> can be executed without producing further messages, + so you are advised to test in a separate development environment. + </p><p> + Setting <code class="varname">plpgsql.extra_warnings</code>, or + <code class="varname">plpgsql.extra_errors</code>, as appropriate, to <code class="literal">"all"</code> + is encouraged in development and/or testing environments. + </p><p> + These additional checks are enabled through the configuration variables + <code class="varname">plpgsql.extra_warnings</code> for warnings and + <code class="varname">plpgsql.extra_errors</code> for errors. Both can be set either to + a comma-separated list of checks, <code class="literal">"none"</code> or + <code class="literal">"all"</code>. The default is <code class="literal">"none"</code>. Currently + the list of available checks includes: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="varname">shadowed_variables</code></span></dt><dd><p> + Checks if a declaration shadows a previously defined variable. + </p></dd><dt><span class="term"><code class="varname">strict_multi_assignment</code></span></dt><dd><p> + Some <span class="application">PL/PgSQL</span> commands allow assigning + values to more than one variable at a time, such as + <code class="command">SELECT INTO</code>. Typically, the number of target + variables and the number of source variables should match, though + <span class="application">PL/PgSQL</span> will use <code class="literal">NULL</code> + for missing values and extra variables are ignored. Enabling this + check will cause <span class="application">PL/PgSQL</span> to throw a + <code class="literal">WARNING</code> or <code class="literal">ERROR</code> whenever the + number of target variables and the number of source variables are + different. + </p></dd><dt><span class="term"><code class="varname">too_many_rows</code></span></dt><dd><p> + Enabling this check will cause <span class="application">PL/PgSQL</span> to + check if a given query returns more than one row when an + <code class="literal">INTO</code> clause is used. As an <code class="literal">INTO</code> + statement will only ever use one row, having a query return multiple + rows is generally either inefficient and/or nondeterministic and + therefore is likely an error. + </p></dd></dl></div><p> + + The following example shows the effect of <code class="varname">plpgsql.extra_warnings</code> + set to <code class="varname">shadowed_variables</code>: +</p><pre class="programlisting"> +SET plpgsql.extra_warnings TO 'shadowed_variables'; + +CREATE FUNCTION foo(f1 int) RETURNS int AS $$ +DECLARE +f1 int; +BEGIN +RETURN f1; +END; +$$ LANGUAGE plpgsql; +WARNING: variable "f1" shadows a previously defined variable +LINE 3: f1 int; + ^ +CREATE FUNCTION +</pre><p> + The below example shows the effects of setting + <code class="varname">plpgsql.extra_warnings</code> to + <code class="varname">strict_multi_assignment</code>: +</p><pre class="programlisting"> +SET plpgsql.extra_warnings TO 'strict_multi_assignment'; + +CREATE OR REPLACE FUNCTION public.foo() + RETURNS void + LANGUAGE plpgsql +AS $$ +DECLARE + x int; + y int; +BEGIN + SELECT 1 INTO x, y; + SELECT 1, 2 INTO x, y; + SELECT 1, 2, 3 INTO x, y; +END; +$$; + +SELECT foo(); +WARNING: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_warnings is active. +HINT: Make sure the query returns the exact list of columns. +WARNING: number of source and target fields in assignment does not match +DETAIL: strict_multi_assignment check of extra_warnings is active. +HINT: Make sure the query returns the exact list of columns. + + foo +----- + +(1 row) +</pre><p> + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 xmlns="http://www.w3.org/1999/xhtml" class="application">PL/pgSQL</span> under the Hood </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.13. Porting from <span xmlns="http://www.w3.org/1999/xhtml" class="productname">Oracle</span> PL/SQL</td></tr></table></div></body></html>
\ No newline at end of file |