1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>36.2.Tips for Developing in PL/pgSQL</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="plpgsql.html" title="Chapter36.PL/pgSQL - SQL Procedural Language">
<link rel="prev" href="plpgsql.html" title="Chapter36.PL/pgSQL - SQL Procedural Language">
<link rel="next" href="plpgsql-structure.html" title="36.3.Structure of PL/pgSQL">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="plpgsql-development-tips"></a>36.2.Tips for Developing in <span class="application">PL/pgSQL</span></h2></div></div></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 as a tool is
<span class="application">PgAccess</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" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="plpgsql-quote-tips"></a>36.2.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.
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">dollar-quoted</span>” string literal (see <a href="sql-syntax.html#sql-syntax-dollar-quoting" title="4.1.2.2.Dollar-Quoted String Constants">Section4.1.2.2, “Dollar-Quoted String Constants”</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 may be useful when translating
pre-dollar quoting code into something more comprehensible.
</p>
<div class="variablelist"><dl>
<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 href="plpgsql-porting.html#plpgsql-porting-ex2" title="Example36.6.Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL">Example36.6, “Porting a Function that Creates Another Function from <span class="application">PL/SQL</span> to <span class="application">PL/pgSQL</span>”</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>
<p> A variant approach is to escape quotation marks in the function body
with a backslash rather than by doubling them. With this method
you'll find yourself writing things like <code class="literal">\'\'</code> instead
of <code class="literal">''''</code>. Some find this easier to keep track of, some
do not.
</p>
</div>
</div></body>
</html>
|
