CODEWITHSUNDEEP
databasepostgresqlplpgsqldollar-signdollar-quoting
vector
vector

Reputation: 7576

What are '$$' used for in PL/pgSQL

Being completely new to PL/pgSQL, what is the meaning of double dollar signs in this function:

CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean AS $$
BEGIN
  IF NOT $1 ~  e'^\\+\\d{3}\\ \\d{3} \\d{3} \\d{3}$' THEN
    RAISE EXCEPTION 'Wrong formated string "%". Expected format is +999 999';
  END IF;
  RETURN true; 
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

I'm guessing that, in RETURNS boolean AS $$, $$ is a placeholder.

The last line is a bit of a mystery: $$ LANGUAGE plpgsql STRICT IMMUTABLE;

By the way, what does the last line mean?

Upvotes: 166

Views: 89305

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

The delimiters $$ and ' are used to create the body which can have one or more SQL statements in a SQL function, PL/pgSQL function, procedure, trigger and event trigger.

For example, you can create a PL/pgSQL function with the delimiter $$ as shown below:

CREATE FUNCTION addition(v1 INTEGER, v2 INTEGER) RETURNS INTEGER AS $$
DECLARE
  result INTEGER;
BEGIN
SELECT v1 + v2 INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;

And, you can create a PL/pgSQL function with the delimiter ' as shown below:

CREATE FUNCTION addition(v1 INTEGER, v2 INTEGER) RETURNS INTEGER AS '
DECLARE
  result INTEGER;
BEGIN
SELECT v1 + v2 INTO result;
RETURN result;
END;
' LANGUAGE plpgsql;

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656716

These dollar signs ($$) are used for dollar quoting, which is in no way specific to function definitions. It can be used to replace single quotes enclosing string literals (constants) anywhere in SQL scripts.

The body of a function happens to be such a string literal. Dollar quoting is a PostgreSQL-specific substitute for single quotes to avoid escaping of nested single quotes (recursively). You can enclose the function body in single-quotes just as well. But then you have to escape all nested single quotes:

CREATE OR REPLACE FUNCTION check_phone_number(text)
  RETURNS boolean
  LANGUAGE plpgsql STRICT IMMUTABLE AS
'
BEGIN
  IF NOT $1 ~  e''^\\+\\d{3}\\ \\d{3} \\d{3} \\d{3}$'' THEN
    RAISE EXCEPTION ''Malformed string: "%". Expected format is +999 999'', $1;
  END IF;
  RETURN true; 
END
';

(Added the missing parameter for RAISE, btw.)

This isn't such a good idea. Use dollar-quoting instead. More specifically, also put a (meaningful) token inside the $$ to avoid confusion with nested quotes in the function body. A common case, actually.

CREATE OR REPLACE FUNCTION check_phone_number(text)
  RETURNS boolean  
  LANGUAGE plpgsql STRICT IMMUTABLE AS
$func$
BEGIN
 ...
END
$func$;

See:

To your second question:
Read the most excellent manual on CREATE FUNCTION to understand the last line of your example.

Upvotes: 241

Captain Coder
Captain Coder

Reputation: 818

The $$ is a delimiter you use to indicate where the function definition starts and ends. Consider the following,

CREATE TABLE <name> <definition goes here> <options go here, eg: WITH OIDS>

The create function syntax is similar, but because you are going to use all sorts of SQL in your function (especially the end of statement ; character), the parser would trip if you didn't delimit it. So you should read your statement as:

CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean AS <code delimited by $$> LANGUAGE plpgsql STRICT IMMUTABLE;

The stuff after the actual definition are options to give the database more information about your function, so it can optimize its usage.

In fact, if you look under "4.1.2.4. Dollar-Quoted String Constants" in the manual, you will see that you can even use characters in between the dollar symbols and it will all count as one delimiter.

Upvotes: 27

Related Questions