In PostgreSQL, you use single quotes for a string constant like this:
select 'String constant';
When a string constant contains a single quote (‘), you need to escape it by doubling up the single quote. For example:
select 'I''m also a string constant';
If you use an old version of PostgreSQL, one can prepend the string constant with E to declare the postfix escape string syntax and use the backslash \ to escape the single quote like this:
select E'I\'m also a string constant';
If a string constant contains a backslash, you need to escape it by using another backslash. The problem arises when the string constant contains many single quotes and backslashes. Doubling every single quote and backslash makes the string constant more difficult to read and maintain. PostgreSQL version 8.0 introduced the dollar quoting feature to make string constants more readable.
The following shows the syntax of the dollar-quoted string constants:
In this syntax, the tag is optional. It may contain zero or many characters. Between the $tag$, you can place any string with single quotes (‘) and backslashes (\). For example:
select $$I'm a string constant that contains a backslash \$$;
In this example, we did not specify the tag between the two dollar signs($). The following example uses the dollar-quoted string constant syntax with a tag:
SELECT $message$I'm a string constant that contains a backslash \$message$;
In this example, we used the string message as a tag between the two dollar signs ($ ).
For the purpose of demonstrating the use of dollar quoted constants, let’s create a function that returns the total no of films in the sample database, ie, dvdrental, through a film_count() function as shown below:
do $$ declare film_count integer; begin select count(*) into film_count from film; raise notice 'The number of films: %', film_count; end; $$;
The following function finds a film by its id:
create function find_film_by_id( id int ) returns film language sql as $$ select * from film where film_id = id; $$;
Now call the function using the below statement:
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.