diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 115 |
1 files changed, 93 insertions, 22 deletions
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index d6688e13f48..cabedc3ff41 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -5034,7 +5034,7 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ </sect2> <sect2 id="plpgsql-extra-checks"> - <title>Additional Compile-time Checks</title> + <title>Additional Compile-time and Run-time Checks</title> <para> To aid the user in finding instances of simple but common problems before @@ -5046,26 +5046,64 @@ a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$ so you are advised to test in a separate development environment. </para> - <para> - These additional checks are enabled through the configuration variables - <varname>plpgsql.extra_warnings</varname> for warnings and - <varname>plpgsql.extra_errors</varname> for errors. Both can be set either to - a comma-separated list of checks, <literal>"none"</literal> or <literal>"all"</literal>. - The default is <literal>"none"</literal>. Currently the list of available checks - includes only one: - <variablelist> - <varlistentry> - <term><varname>shadowed_variables</varname></term> - <listitem> - <para> - Checks if a declaration shadows a previously defined variable. - </para> - </listitem> - </varlistentry> - </variablelist> + <para> + Setting <varname>plpgsql.extra_warnings</varname>, or + <varname>plpgsql.extra_errors</varname>, as appropriate, to <literal>"all"</literal> + is encouraged in development and/or testing environments. + </para> + + <para> + These additional checks are enabled through the configuration variables + <varname>plpgsql.extra_warnings</varname> for warnings and + <varname>plpgsql.extra_errors</varname> for errors. Both can be set either to + a comma-separated list of checks, <literal>"none"</literal> or + <literal>"all"</literal>. The default is <literal>"none"</literal>. Currently + the list of available checks includes: + <variablelist> + <varlistentry> + <term><varname>shadowed_variables</varname></term> + <listitem> + <para> + Checks if a declaration shadows a previously defined variable. + </para> + </listitem> + </varlistentry> - The following example shows the effect of <varname>plpgsql.extra_warnings</varname> - set to <varname>shadowed_variables</varname>: + <varlistentry> + <term><varname>strict_multi_assignment</varname></term> + <listitem> + <para> + Some <application>PL/PgSQL</application> commands allow assigning + values to more than one variable at a time, such as + <command>SELECT INTO</command>. Typically, the number of target + variables and the number of source variables should match, though + <application>PL/PgSQL</application> will use <literal>NULL</literal> + for missing values and extra variables are ignored. Enabling this + check will cause <application>PL/PgSQL</application> to throw a + <literal>WARNING</literal> or <literal>ERROR</literal> whenever the + number of target variables and the number of source variables are + different. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><varname>too_many_rows</varname></term> + <listitem> + <para> + Enabling this check will cause <application>PL/PgSQL</application> to + check if a given query returns more than one row when an + <literal>INTO</literal> clause is used. As an <literal>INTO</literal> + 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. + </para> + </listitem> + </varlistentry> + </variablelist> + + The following example shows the effect of <varname>plpgsql.extra_warnings</varname> + set to <varname>shadowed_variables</varname>: <programlisting> SET plpgsql.extra_warnings TO 'shadowed_variables'; @@ -5081,8 +5119,41 @@ LINE 3: f1 int; ^ CREATE FUNCTION </programlisting> - </para> - </sect2> + The below example shows the effects of setting + <varname>plpgsql.extra_warnings</varname> to + <varname>strict_multi_assignment</varname>: +<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 do 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 do 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) +</programlisting> + </para> + </sect2> </sect1> <!-- **** Porting from Oracle PL/SQL **** --> |