What are ‘$$’ used for in PL/pgSQL

by

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 could enclose the function body in single-quotes just as well. But then you’d have to escape all single-quotes in the body:

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'';
  END IF;
  RETURN true; 
END
';

This isn’t such a good idea. Use dollar-quoting instead. More specifically, also put a token between the $$ to make each pair unique – you might want to use nested dollar-quotes inside the function body. I do that a lot, actually.

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

See:

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

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. The forgotten assignment operator “=” and the commonplace “:=”
  3. Truncating all tables in a Postgres database
  4. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  5. How to set value of composite variable field using dynamic SQL
  6. How do I do large non-blocking updates in PostgreSQL?
  7. Execute a dynamic crosstab query
  8. Is there a way to disable function overloading in Postgres
  9. Can I make a plpgsql function return an integer without using a variable?
  10. PostgreSQL function returning multiple result sets
  11. Dynamic SQL (EXECUTE) as condition for IF statement
  12. Returning from a function with OUT parameter
  13. SELECT raises exception in PL/pgSQL function
  14. Test for null in function with varying parameters
  15. Return SETOF rows from PostgreSQL function
  16. Error when setting n_distinct using a plpgsql variable
  17. Postgres FOR LOOP
  18. Dynamically generate columns for crosstab in PostgreSQL
  19. Function to loop through and select data from multiple tables
  20. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  21. SQL Sub queries in check constraint
  22. Cursor based records in PostgreSQL
  23. ERROR: unterminated quoted string at or near
  24. How can I send some http request from postgresql function or trigger
  25. Select from a table variable
  26. Hashing a String to a Numeric Value in PostgreSQL
  27. Loop on tables with PL/pgSQL in Postgres 9.0+
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment