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.
$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$);
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.