PostgreSQL 7.4beta4 Documentation
Prev | Fast Backward | Chapter 39. PL/pgSQL - SQL Procedural Language | Fast Forward | Next

# 39.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:

    CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS '
    	  ....
    end;
    ' LANGUAGE plpgsql;

While running psql, you can load or reload such a function definition file with

    \i filename.sql

and then immediately issue SQL commands to test the function.

Another good way to develop in PL/pgSQL is using 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.

## 39.2.1. Handling of Quote Marks

Since the code of a PL/pgSQL function is specified in `CREATE FUNCTION` as a string literal, single quotes inside the function body must be escaped by doubling them. This can lead to rather complicated code at times, especially if you are writing a function that generates other functions, as in the example in Section 39.6.4. This chart may be useful as a summary of the needed numbers of quote marks in various situations.

1 quote mark

  To begin and end the function body, for example:

      CREATE FUNCTION foo() RETURNS integer AS '...'
          LANGUAGE plpgsql;

  Anywhere within the function body, quote marks must appear in pairs.

2 quote marks

  For string literals inside the function body, for example:

      a_output := ''Blah'';
      SELECT * FROM users WHERE f_name=''foobar'';

  The second line is seen by PL/pgSQL as

      SELECT * FROM users WHERE f_name='foobar';

4 quote marks

  When you need a single quote in a string constant inside the function body, for example:

      a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''

  The value actually appended to a_output would be: AND name LIKE 'foobar' AND xyz.

6 quote marks

  When a single quote in a string inside the function body is adjacent to the end of that string constant, for example:

      a_output := a_output || '' AND name LIKE ''''foobar''''''

  The value appended to a_output would then be: AND name LIKE 'foobar'.

10 quote marks

  When you want two single quotes in a string constant (which accounts for 8 quotes) 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. For example:

      a_output := a_output || '' if v_'' ||
          referrer_keys.kind || '' like ''''''''''
          || referrer_keys.key_string || ''''''''''
          then return ''''''  || referrer_keys.referrer_type
          || ''''''; end if;'';

  The value of a_output would then be:

      if v_... like ''...'' then return ''...''; end if;

A different approach is to escape quote 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.

Prev | Home | Next
PL/pgSQL - SQL Procedural Language | Up | Structure of PL/pgSQL