PostgreSQL Anonymous Code Block, with DO

PostgreSQL offers code blocks where you can execute complex tasks including creating loops, calculations, and other SQL queries. The block structure follows a specific format, and the DO keyword symbolizes the start of the code block until the END keyword is met. This block structure is an excellent way of working with anonymous code blocks to perform different tasks in your database. We will discuss various examples of working with PostgreSQL anonymous code with Do to ensure that you get comfortable with it. Let’s begin!

Understanding the Structure of PostgreSQL Code Blocks

A code block on PostgreSQL follows the following syntax:

In the DECLARE section, you declare the variables that you want to use with the code block. In the BEGIN section, it is where you perform the actions such as SQL queries. It is a mandatory section in the code block. Lastly, the exception is utilized when defining how to handle the errors. The END keyword shows the end of the block. The label represents the anonymous block.

Examples of PostgreSQL Anonymous Code Blocks

Having understood the structure to follow, let’s give different examples of its implementations.

Example 1: A Simple Code Block

This example shows a code block with no variables and one that only displays a message to a user using the RAISE NOTICE statement.

With PostgreSQL, your code block will execute instantly when you press the “Enter” key.

Example 2: Anonymous Code Block

In the first example, we didn’t add an anonymous code block. Such a case assumes that the entire block is anonymous, and you can’t have a semi-block inside it as you will have no way of referencing it.

The following example creates the “main_block”. Note that you must enclose it as demonstrated in the following block structure:

Moreover, when adding the END keyword, you must specify the name of the anonymous code block that is terminating.

Example 3: Anonymous Code Block with a Variable

Working with variables is handy inside a code block. Variables are declared in the DECLARE section. Although you can initialize them in the same block, most cases require you to initialize them in the BEGIN section.

You can reference the variables by specifying the name of the anonymous code block where they were initialized. That way, if you have numerous blocks, such as a parent and child block, you won’t have a variable confusion which raises errors.

When declaring variables, you must add the variable type for PostgreSQL to understand what data to expect and store in that variable. For this example, we have an integer variable. We increment its value and print a message to the terminal.

Example 4: PostgreSQL Anonymous Code Block Working with a Table

When you have tables in your database, you can run a query to reference the values in your table. For instance, this example uses the following table for reference:

We create a SELECT query inside our code block to get the value of a given entry that matches the specified condition. The extracted value is then saved INTO the declared variable and a message is printed that shows the retrieved value.

Still, on tables, it’s possible to run a query that creates a table and inserts values into it. Watch how the following PostgreSQL anonymous code block creates a table named “new_1” and runs an insert query. The query runs successfully.

When we check the available tables in the database, we can see that the table was created. Moreover, checking its entries, we get the same ones that we inserted in the code block. Ideally, you can run any SQL, provided that it is correct and its values are captured as expected.

Example 5: Working with an Anonymous Sub-Block Code

Sometimes, you may get a case where you want an outer block, the parent block, and other sub-blocks inside it. Your code is what determines how the sub-block will play out. Again, you can have an outer block that shares the same variable name with the sub-block. When referencing the variable, you must specify the owner block.

In the following example, we have the “parent_block” as our outer block. We add another DECLARE and BEGIN sections to hold the sub-block. Moreover, the END keyword is used twice to close the inner and outer blocks.

That’s how you work with sub-blocks in PostgreSQL anonymous code blocks.

Conclusion

As a PostgreSQL user, you should understand about the anonymous code blocks and how to use them. The insights that are given in this guide and the examples are an easy guide to help with your understanding. Keep practicing the examples, and you will soon get comfortable working with PostgreSQL anonymous code blocks.