Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What are Dollar Quoted Strings Constants?
3.
What are Dollar Quoted Strings Constants in PostgreSQL? 
4.
Examples of the Dollar-Quoted Strings
4.1.
Using the Dollar-Quoted String Constants in Functions
4.2.
Using the dollar-quoted String Constants in an Anonymous Block
4.3.
Using the Dollar-Quoted String Constants in Stored Procedures
5.
What is the Block Structure in Postgresql?
6.
Frequently Asked Questions
6.1.
What is the common mistake that causes syntax errors in a Block?
6.2.
What is the purpose of Hidden Outer Block in PL/pgSQL?
6.3.
What do you mean by Anonymous blocks in PostgreSQL?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

What are Dollar Quoted String Constants and Block Structure in Postgresql?

Author Nidhi Kumari
1 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

The POSTGRES project at the University of California, Berkeley, is where PostgreSQL first emerged in 1986, and the primary platform has completed more than 36 years of active evolution. PostgreSQL is an object-relational database system. It supports object inheritance and provides more complex data types but also makes using PostgreSQL more difficult.

In this article, we will learn what dollar-quoted string constants and block structures are in Postgresql.

What are Dollar Quoted String Constants and Block Structure in Postgresql?

What are Dollar Quoted Strings Constants?

Two dollar signs( $$) are used for Dollar quoting. Anywhere in PostgreSQL, we can use it to replace the single quotes around string literals (constants). Such a string literal occurs to be the body of a function.

A series of zero or more characters in single quotation marks is a string literal. These are some examples of string literals:

'Coding Ninjas!'

'26-Jan-2022'

'Be more than a Programmer, Be a Coding Ninja!'

Since PostgreSQL is case-sensitive, case matters in PostgreSQL when using string literals. For example, PostgreSQL views the subsequent literals as different:

'Coding Ninjas!'

'coding ninjas!'

The Syntax for the Dollar Quoted Strings is as follows:

$tag$ String $tag$

 

Here, the String is a random string of characters separated by a pair of dollar signs ($$).

The tag is optional. It could have one character or multiple. You can insert any string using single quotes (') and backslashes (\) between the $tag$.

Without tag: select $$I'm a ninja coder that uses the CodingNinjas Platform \$$;

With tag: select $message$I'm a ninja coder that uses the CodingNinjas Platform \$message$;

The content of dollar-quoted strings is taken as a literal. Within a dollar-quoted string, characters like the single quote, backslash, and dollar sign have no special significance.

Note: If there is no whitespace between a dollar-quoted string and the word or phrase it follows, the dollar-quoting delimiter is regarded as a component of the word or phrase it follows.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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 ];

 

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 AlgorithmsCompetitive ProgrammingSystem 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!!

Previous article
Conditionals and Control Flow in PostgreSQL
Next article
How to Create Stored Procedures In PostgreSQL?
Live masterclass