Dollar-quoting for escaping single quotes

PostgreSQL has a feature called dollar-quoting, which allows you to include a body of text without escaping the single quotes. This feature has existed for quite some time. You've probably seen this in action when defining functions for example:

CREATE OR REPLACE FUNCTION hello_world(param_your_name text)
RETURNS text AS
$$
SELECT 'Hello world. My name is ' || param_your_name || '.';
$$
language sql STRICT;

Which is easier to read, than the equivalent escape quoted function:

CREATE OR REPLACE FUNCTION hello_world(param_your_name text)
RETURNS text AS
'
SELECT ''Hello world. My name is '' || param_your_name || ''.'';
'
language sql STRICT;

I had almost forgotten about the usefulness of dollar-quoting outside of defining functions, until Vicky Vergara reminded me when we were working on some examples for our recently published book pgRouting: A Practical Guide. pgRouting applications are a perfect use-case for this feature since pgRouting has a lot of functions that take as input an SQL statement. If you have SQL statements that contain quotes, it's a pain to have to escape all the quotes in them to pass as input to a function.

You can use this feature pretty much anywhere where text is required. My favorites besides the obvious function bodies are DO commands, variables within functions, function inputs, and function/table/column comments.

Did you know that within dollar quoting, you can quote again using named dollar quoting. Named dollar quoting is using text in-between $$s as the delimiter and you can use any text you want. For example $sql$SELECT 'Johnny be good';$sql$. The key is to use a named dollar-quoting delimiter that is different from your outer delimiter. How does this work:

Here is a function that takes an sql expression that should return a text and executes it:

CREATE OR REPLACE FUNCTION sql_expression(param_sql text) 
RETURNS text AS
$$ 
DECLARE var_result text;
BEGIN
    EXECUTE param_sql INTO var_result;
    RETURN var_result;
END;
$$
language 'plpgsql' STABLE STRICT;

We can then use the function as follows:

SELECT sql_expression($sql$SELECT hello_world($phrase$Regina's elephant's dog$phrase$) 
    || $phrase$ I made a cat's meow today.$phrase$ $sql$);

Which outputs:

Hello world. My name is Regina's elephant's dog. I made a cat's meow today.

If you were to do this using escaping quotes, you'd have to escape out the inner quotes twice as follows:

SELECT sql_expression('SELECT hello_world(''Regina''''s elephant''''s dog'') 
    || '' I made a cat''''s meow today.'' ');

Which makes it difficult to just copy and paste the text from your scrap book of phrases.

This topic is one of the PostgreSQLisms covered in our upcoming book PostgreSQL: Up and Running 3rd Edition currently available in O'Reilly Early Release program.