What are Dollar Quoted Strings Constants in PostgreSQL?
Sometimes, the syntax used to specify string literals can be difficult. One of the cases is when a string contains a single quote('). You must double the single quotation to escape it when a string constant has one. For example:
select 'I''m also a Ninja coder';
Older Version of PostgreSQL: To specify the postfix escape string syntax and escape the single quote, you can prepend the string constant with E, as shown below.
select E'I\'m also a Ninja coder';
You must use another backslash to escape any backslashes in string constants. An issue occurs when the string constant has several single quotes and backslashes. Reading and maintaining the string constant is more challenging when each quotation and backslash is doubled.
PostgreSQL version 8.0: Dollar quoted string constants are introduced in Version 8.0. As mentioned above, the syntax of the dollar-quoted string constants is as follows:
$tag$ String $tag$
PostgreSQL offers dollar quoting in some cases to enable more meaningful queries. Although the SQL standard does not support dollar quoting, it is frequently more practical than using the single quote syntax to write complex string literals.
Dollar-quoting is a PostgreSQL-specific alternative to single quotes (recursively) to prevent escaping of nested single quotes. The function body could be enclosed in single quotes. However, you would then need to escape every single quote within the body.
Let’s understand the concept by considering examples of Dollar quoted String Constants in PostgreSQL.
Examples of the Dollar-Quoted Strings
To understand the concept of Dollar quoted strings, we will see examples of dollar-quoted strings in various cases:
-
Using the dollar-quoted string constants in functions.
-
Using the dollar-quoted string constants in an anonymous block.
- Using the dollar-quoted string constants in stored procedures.
Using the Dollar-Quoted String Constants in Functions
The CREATE FUNCTION statement, which enables you to create a user-defined function, has the following syntax:
create function My_function(parameters_list)
returns datatype
language My_lang
as
'function_body'
The function_body in this syntax is a constant string. For example, the following function locates a course based on its ID:
create function find_course_by_id(
id int
) returns course
language sql
as
'select * from course
where course_id = id';
As you can see, single quotes are used to enclose the body of the find_course_by_id() function. If the function has a lot of statements, it becomes complex to read. You can avoid this by using string constant syntax enclosed in dollar signs:
create function find_course_by_id(
id int
) returns course
language sql
as
$$
select * from course
where course_id = id
$$;
Now, you can insert any code between $$ and $$(tags) without using single quotes or backslashes to skip them.
Using the dollar-quoted String Constants in an Anonymous Block
The anonymous block in PostgreSQL is shown in the following example:
do
'declare
course_count integer;
begin
select count(*) into course_count
from course;
raise notice ''The number of courses: %'', course_count;
end;';
Single quotes must be used to enclose the block's code. If it contains a single quote, you must double it to get rid of it.
raise notice ''The number of courses: %'', course_count;
To simplify, we can use the dollar-quoted strings. Single quotes must be used to enclose the block's code. If it contains a single quote, you must double it to get rid of it. You don't need to escape this example's single quotes or backslashes.
do
$$
declare
course_count integer;
begin
select count(*) into course_count
from course;
raise notice 'The number of courses: %', course_count;
end;
$$;
Using the Dollar-Quoted String Constants in Stored Procedures
The code for the Stored procedure is as follows:
create procedure procedure_name(parameters_list)
language language_name
as '
-- The body of the stored procedure
';
Similar to the previous cases, the stored procedure syntax for dollar-quoted string constants looks like this:
create procedure procedure_name(parameters_list)
language language_name
as $$
-- The body of the stored procedure
$$;
What is the Block Structure in Postgresql?
PostgreSQL is a block-structured language. A function body must have a block as its entire text. Following is an example of how to define a block:
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
Statement 1
Statement 2
………
Statement n
END [ label ];
A label is required only when using the block in an EXIT statement or to qualify the names of the variables defined in the block. If a label is provided after END, it must be the same as the label at the start of the block.
Each block has two main sections:
-
Declaration: It is optional.
-
Body: It is a required section.
A semicolon(;) marks the end of each declaration and each statement within a block. The END statement that ends a function body is not required to have a semicolon, but blocks within other blocks must have one, as shown above.
Like regular SQL, PostgreSQL code supports comments similar manner. A comment extending to the line's end is introduced by a double dash (--).
The case is irrelevant for any keywords, i.e., Keywords are case-insensitive. Similar to how they are in regular SQL instructions, identifiers are automatically changed to lowercase if they are not double-quoted.
The example of a block in PostgreSQL is as follows:
do
$$
<<first_block>>
declare
course_count integer := 0;
begin
select count(*) into course_count
from course;
-- display the message
raise notice 'The number of courses is: %', course_count;
end first_block
$$;
Output
Notice: The number of courses is: 1
A subblock can be any statement included in the block's statement section. Subblocks can be used to localise variables to a set of sentences or for logical grouping.
For the length of the subblock, variables declared in outer blocks with similar names are hidden; however, if you qualify the names of the outer blocks' variables with the block's label, you can still access the outer blocks' variables. For example:
CREATE FUNCTION Myfunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
number integer := 10;
BEGIN
RAISE NOTICE 'The Number here is %', number; -- Prints 10
number := 30;
--
-- Create a subblock
--
DECLARE
number integer := 50;
BEGIN
RAISE NOTICE 'The Number here is %', number; -- Prints 50
RAISE NOTICE 'The Outer Number here is %', outerblock.number; -- Prints 30
END;
RAISE NOTICE 'The Number here is %', number; -- Prints 30
RETURN number;
END;
$$ LANGUAGE plpgsql;
Frequently Asked Questions
What is the common mistake that causes syntax errors in a Block?
The practice of writing a semicolon just after the word BEGIN of a block is a common error. This violates syntax rules and should not be done. Also, the END statement that ends a function body is not required to have a semicolon, but blocks within other blocks must have one.
What is the purpose of Hidden Outer Block in PL/pgSQL?
The body of every PL/pgSQL function is surrounded by a hidden "outer block." The declarations of the function's parameters, if any, and some unique variables, like FOUND, are provided in this block. The function name appears in the outer block, making it possible to qualify parameters and unique variables using the function name.
What do you mean by Anonymous blocks in PostgreSQL?
Unnamed blocks are referred to as anonymous blocks. An anonymous block is only suitable for one use because it is not kept on the Database server. An executable statement, the PL/SQL anonymous block statement, can hold both SQL and PL/SQL control statements. In a scripting language, one can use it to build procedural logic. The data server can build and execute this statement in PL/SQL environments.
Conclusion
We have discussed the dollar-quoted string constants and block structures in PostgreSQL. To make the code more readable, user-defined functions and stored procedures must always employ quoted-dollar string constants. If you want to avoid escaping single quotes or backslashes, use quoted-dollar string constants.
We hope this blog has helped you. We recommend you visit our articles on different topics of PostgreSQL, such as
🔥 PostgreSQL Datatypes.
🔥 PostgreSQL Create Database.
🔥 Getting Started with PostgreSQL.
If you liked our article, do upvote our article and help other ninjas grow. You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, System Design, and many more!
Check out this problem - Multiply Strings
Head over to our practice platform Coding Ninjas Studio to practice top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!!
Happy Reading!!
84+ registered 