What is the "$$" used in PL / pgSQL

Question

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 assume 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?

Answer 1

Dollar signs are used for dollar quotation and do not depend on the definition of functions . It can be used to replace single quotes almost anywhere in SQL scripts.

The body of the function is a string literal, which must be enclosed in single quotes. The dollar column is a PostgreSQL-specific replacement for single quotes to avoid quoting problems inside the function body. You can also write a definition of your function with single quotes. But then you have to avoid all single quotes in the body:

 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 ''Malformed string "%". Expected format is +999 999''; END IF; RETURN true; END ' LANGUAGE plpgsql STRICT IMMUTABLE; 

This is not such a good idea. Instead, use dollar quotation, or rather, place the token between $$ to make it unique - you can also use $ -quotes inside the function body. I actually do it.

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

More details:

• Paste text with single quotes in PostgreSQL

Regarding your second question:
Read the great CREATE FUNCTION tutorial to understand the last line of your example.

Answer 2

$$ is the delimiter you use to indicate where the function definition begins and ends. Consider the following:

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

The syntax of the create function is similar, but since you are going to use all kinds of SQL in your function (especially the end of a statement, a character), the parser will be disabled if you do not separate it. Therefore, you should read your expression as:

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

Material after the actual definition is an option to provide the database with additional information about your function, so it can optimize its use.

In fact, if you look in the section “4.1.2.2. Corrected Strings in Dollars” in the manual, you will see that you can even use the characters between the characters of the dollar, and all of them will be considered as one separator.