summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/plpgsql-development-tips.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/plpgsql-development-tips.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/plpgsql-development-tips.html')
-rw-r--r--doc/src/sgml/html/plpgsql-development-tips.html228
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..afadc4e
--- /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 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 15.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 /></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 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 15.5 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> \ No newline at end of file