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.
PostGIS in Action
About the Authors
Consulting
