begriffs open source - ai-pg/blob - full-docs/txt/plpgsql-development-tips.txt

   1
   2 41.12. Tips for Developing in PL/pgSQL #
   3
   4    41.12.1. Handling of Quotation Marks
   5    41.12.2. Additional Compile-Time and Run-Time Checks
   6
   7    One good way to develop in PL/pgSQL is to use the text editor of your
   8    choice to create your functions, and in another window, use psql to
   9    load and test those functions. If you are doing it this way, it is a
  10    good idea to write the function using CREATE OR REPLACE FUNCTION. That
  11    way you can just reload the file to update the function definition. For
  12    example:
  13 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
  14           ....
  15 $$ LANGUAGE plpgsql;
  16
  17    While running psql, you can load or reload such a function definition
  18    file with:
  19 \i filename.sql
  20
  21    and then immediately issue SQL commands to test the function.
  22
  23    Another good way to develop in PL/pgSQL is with a GUI database access
  24    tool that facilitates development in a procedural language. One example
  25    of such a tool is pgAdmin, although others exist. These tools often
  26    provide convenient features such as escaping single quotes and making
  27    it easier to recreate and debug functions.
  28
  29 41.12.1. Handling of Quotation Marks #
  30
  31    The code of a PL/pgSQL function is specified in CREATE FUNCTION as a
  32    string literal. If you write the string literal in the ordinary way
  33    with surrounding single quotes, then any single quotes inside the
  34    function body must be doubled; likewise any backslashes must be doubled
  35    (assuming escape string syntax is used). Doubling quotes is at best
  36    tedious, and in more complicated cases the code can become downright
  37    incomprehensible, because you can easily find yourself needing half a
  38    dozen or more adjacent quote marks. It's recommended that you instead
  39    write the function body as a “dollar-quoted” string literal (see
  40    Section 4.1.2.4). In the dollar-quoting approach, you never double any
  41    quote marks, but instead take care to choose a different dollar-quoting
  42    delimiter for each level of nesting you need. For example, you might
  43    write the CREATE FUNCTION command as:
  44 CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
  45           ....
  46 $PROC$ LANGUAGE plpgsql;
  47
  48    Within this, you might use quote marks for simple literal strings in
  49    SQL commands and $$ to delimit fragments of SQL commands that you are
  50    assembling as strings. If you need to quote text that includes $$, you
  51    could use $Q$, and so on.
  52
  53    The following chart shows what you have to do when writing quote marks
  54    without dollar quoting. It might be useful when translating pre-dollar
  55    quoting code into something more comprehensible.
  56
  57    1 quotation mark #
  58           To begin and end the function body, for example:
  59
  60 CREATE FUNCTION foo() RETURNS integer AS '
  61           ....
  62 ' LANGUAGE plpgsql;
  63
  64           Anywhere within a single-quoted function body, quote marks must
  65           appear in pairs.
  66
  67    2 quotation marks #
  68           For string literals inside the function body, for example:
  69
  70 a_output := ''Blah'';
  71 SELECT * FROM users WHERE f_name=''foobar'';
  72
  73           In the dollar-quoting approach, you'd just write:
  74
  75 a_output := 'Blah';
  76 SELECT * FROM users WHERE f_name='foobar';
  77
  78           which is exactly what the PL/pgSQL parser would see in either
  79           case.
  80
  81    4 quotation marks #
  82           When you need a single quotation mark in a string constant
  83           inside the function body, for example:
  84
  85 a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
  86
  87           The value actually appended to a_output would be: AND name LIKE
  88           'foobar' AND xyz.
  89
  90           In the dollar-quoting approach, you'd write:
  91
  92 a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
  93
  94           being careful that any dollar-quote delimiters around this are
  95           not just $$.
  96
  97    6 quotation marks #
  98           When a single quotation mark in a string inside the function
  99           body is adjacent to the end of that string constant, for
 100           example:
 101
 102 a_output := a_output || '' AND name LIKE ''''foobar''''''
 103
 104           The value appended to a_output would then be: AND name LIKE
 105           'foobar'.
 106
 107           In the dollar-quoting approach, this becomes:
 108
 109 a_output := a_output || $$ AND name LIKE 'foobar'$$
 110
 111    10 quotation marks #
 112           When you want two single quotation marks in a string constant
 113           (which accounts for 8 quotation marks) and this is adjacent to
 114           the end of that string constant (2 more). You will probably only
 115           need that if you are writing a function that generates other
 116           functions, as in Example 41.10. For example:
 117
 118 a_output := a_output || '' if v_'' ||
 119     referrer_keys.kind || '' like ''''''''''
 120     || referrer_keys.key_string || ''''''''''
 121     then return ''''''  || referrer_keys.referrer_type
 122     || ''''''; end if;'';
 123
 124           The value of a_output would then be:
 125
 126 if v_... like ''...'' then return ''...''; end if;
 127
 128           In the dollar-quoting approach, this becomes:
 129
 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
 135           where we assume we only need to put single quote marks into
 136           a_output, because it will be re-quoted before use.
 137
 138 41.12.2. Additional Compile-Time and Run-Time Checks #
 139
 140    To aid the user in finding instances of simple but common problems
 141    before they cause harm, PL/pgSQL provides additional checks. When
 142    enabled, depending on the configuration, they can be used to emit
 143    either a WARNING or an ERROR during the compilation of a function. A
 144    function which has received a WARNING can be executed without producing
 145    further messages, so you are advised to test in a separate development
 146    environment.
 147
 148    Setting plpgsql.extra_warnings, or plpgsql.extra_errors, as
 149    appropriate, to "all" is encouraged in development and/or testing
 150    environments.
 151
 152    These additional checks are enabled through the configuration variables
 153    plpgsql.extra_warnings for warnings and plpgsql.extra_errors for
 154    errors. Both can be set either to a comma-separated list of checks,
 155    "none" or "all". The default is "none". Currently the list of available
 156    checks includes:
 157
 158    shadowed_variables #
 159           Checks if a declaration shadows a previously defined variable.
 160
 161    strict_multi_assignment #
 162           Some PL/pgSQL commands allow assigning values to more than one
 163           variable at a time, such as SELECT INTO. Typically, the number
 164           of target variables and the number of source variables should
 165           match, though PL/pgSQL will use NULL for missing values and
 166           extra variables are ignored. Enabling this check will cause
 167           PL/pgSQL to throw a WARNING or ERROR whenever the number of
 168           target variables and the number of source variables are
 169           different.
 170
 171    too_many_rows #
 172           Enabling this check will cause PL/pgSQL to check if a given
 173           query returns more than one row when an INTO clause is used. As
 174           an INTO statement will only ever use one row, having a query
 175           return multiple rows is generally either inefficient and/or
 176           nondeterministic and therefore is likely an error.
 177
 178    The following example shows the effect of plpgsql.extra_warnings set to
 179    shadowed_variables:
 180 SET plpgsql.extra_warnings TO 'shadowed_variables';
 181
 182 CREATE FUNCTION foo(f1 int) RETURNS int AS $$
 183 DECLARE
 184 f1 int;
 185 BEGIN
 186 RETURN f1;
 187 END;
 188 $$ LANGUAGE plpgsql;
 189 WARNING:  variable "f1" shadows a previously defined variable
 190 LINE 3: f1 int;
 191         ^
 192 CREATE FUNCTION
 193
 194    The below example shows the effects of setting plpgsql.extra_warnings
 195    to strict_multi_assignment:
 196 SET plpgsql.extra_warnings TO 'strict_multi_assignment';
 197
 198 CREATE OR REPLACE FUNCTION public.foo()
 199  RETURNS void
 200  LANGUAGE plpgsql
 201 AS $$
 202 DECLARE
 203   x int;
 204   y int;
 205 BEGIN
 206   SELECT 1 INTO x, y;
 207   SELECT 1, 2 INTO x, y;
 208   SELECT 1, 2, 3 INTO x, y;
 209 END;
 210 $$;
 211
 212 SELECT foo();
 213 WARNING:  number of source and target fields in assignment does not match
 214 DETAIL:  strict_multi_assignment check of extra_warnings is active.
 215 HINT:  Make sure the query returns the exact list of columns.
 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
 220  foo
 221 -----
 222
 223 (1 row)