9
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

The above code is taken from the Postgresql website. However I do not understand why $$ is used. I have seen it at multiple examples online and none of them actually explains why that is used. Or is it even necessary?

lospejos
  • 1,976
  • 3
  • 19
  • 35
Mox
  • 2,355
  • 2
  • 26
  • 42
  • 2
    http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING –  Jul 08 '15 at 06:48

1 Answers1

9

From the manual for the create function statement:

definition

A string constant defining the function; the meaning depends on the language. It can be an internal function name, the path to an object file, an SQL command, or text in a procedural language.

It is often helpful to use dollar quoting (see Section 4.1.2.4) to write the function definition string, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the function definition must be escaped by doubling them.

Section 4.1.2.4 explains the dollar quoting:

A dollar-quoted string constant consists of a dollar sign ($), an optional "tag" of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string content, a dollar sign, the same tag that began this dollar quote, and a dollar sign. For example, here are two different ways to specify the string "Dianne's horse" using dollar quoting

  • but 1 question, is the dollar quoted string compulsory for all postgresql function? it seems that all the functions has that behind "AS" – Mox Jul 08 '15 at 06:54
  • 1
    @mox: no it's not compulsary. The first example in the manual does not use dollar quoting: http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-EXAMPLES –  Jul 08 '15 at 06:55
  • Ah Thanks. I will accepting the answer in a moment. =) – Mox Jul 08 '15 at 06:57