| PostgreSQL 8.0.1 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 35. PL/pgSQL - SQL Procedural Language | Fast Forward | Next |
35.2. Tips for Developing in PL/pgSQL
One good way to develop in PL/pgSQL is to use the text editor of your choice to create your functions, and in another window, use psql to load and test those functions. If you are doing it this way, it is a good idea to write the function using CREATE OR REPLACE FUNCTION. That way you can just reload the file to update the function definition. For example:
While running psql, you can load or reload such a function definition file with
and then immediately issue SQL commands to test the function.
Another good way to develop in PL/pgSQL is with a GUI database access tool that facilitates development in a procedural language. One example of such as a tool is PgAccess, although others exist. These tools often provide convenient features such as escaping single quotes and making it easier to recreate and debug functions.
35.2.1. Handling of Quotation Marks
The code of a PL/pgSQL function is specified in CREATE FUNCTION 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 "dollar-quoted" string literal (see Section 4.1.2.2). 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 CREATE FUNCTION command as
Within this, you might use quote marks for simple literal strings in SQL commands and $$ to delimit fragments of SQL commands that you are assembling as strings. If you need to quote text that includes $$, you could use $Q$, and so on.
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.
- 1 quotation mark
To begin and end the function body, for example:
Anywhere within a single-quoted function body, quote marks must appear in pairs.
- 2 quotation marks
For string literals inside the function body, for example:
In the dollar-quoting approach, you'd just write
which is exactly what the PL/pgSQL parser would see in either case.
- 4 quotation marks
When you need a single quotation mark in a string constant inside the function body, for example:
The value actually appended to a_output would be: AND name LIKE 'foobar' AND xyz.
In the dollar-quoting approach, you'd write
being careful that any dollar-quote delimiters around this are not just $$.
- 6 quotation marks
When a single quotation mark in a string inside the function body is adjacent to the end of that string constant, for example:
The value appended to a_output would then be: AND name LIKE 'foobar'.
In the dollar-quoting approach, this becomes
- 10 quotation marks
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 Example 35-5. For example:
The value of a_output would then be:
In the dollar-quoting approach, this becomes
where we assume we only need to put single quote marks into a_output, because it will be re-quoted before use.
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 \'\' instead of ''''. Some find this easier to keep track of, some do not.
