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