Postgres Dollar Quoting
Postgre has a feature called Dollar-Quoting. This feature is used to include any text without escaping single quotes ( ‘ )
Please see the below example
SELECT 'Andrew''s Pen'; RESULT -> Andrew's pen
In above example you might have noticed that in order to add a single quote into a text string, there should be an escape quote as well. By using dollar quoting feature we can exclude additional quotes
SELECT $$Andrew’s Pen$$; RESULT -> Andrew's pen
What if there are dollar signs in the text string
SELECT $$Andrew’s Millions Dollar Pen $$$$$$
Expected Value : Andrew’s Millions Dollar Pen $$$$
Actual Value: error
You can solve this by using dollar quotes with a token
SELECT $myCustomToken$Andrew's Millions Dollar Pen $$$$$myCustomToken$
Expected Value : Andrew’s Millions Dollar Pen $$$$
Actual Value: Andrew’s Millions Dollar Pen $$$$
In this case, expected value and actual value are same. Here we have used a custom token surrounded by two dollar marks($myCustomToken$). Now you can add as much as dollar quotes inside.
Double Quoting Usages
- Avoid double quoting hell
SELECT '''a'', ''b'', ''c'', ''d'', ''e'', ''f''';
2. Easy to define postgres function
create or replace function custom_add (i int, j int) returns integer as '
begin
return i + j;
end;
' language plpgsql;select custom_add(10,100); -> result 110
Postgres function body is defined inside single quotes. It is easier to work with dollar quoting for functions specially when quotes are used inside functions.
create or replace function custom_add (i int, j int) returns integer as $$
begin
return i + j;
end;