Using PostgreSQL Anonymous Code Blocks

The full title of this post is “Using PostgreSQL Anonymous Code Blocks for More Productive Development,” since improved productivity is a primary benefit from using them.  This post should also answer the following two questions:

  1. How to create local variables in PostgreSQL
  2. How to output multiple records from PostgreSQL Anonymous Blocks

In SQL Server it’s easy to use inline variables in query editors.

declare @One integer = 1
declare @Two integer = 2
select @One + @Two as SUM

PostgreSQL is a bit more of a stickler on using inline variables. That’s where Anonymous Code Blocks come in, or “DO” blocks.  An anonymous code block is treated as though it were the body of a function with no parameters and always returns void. It is parsed and executed a single time.

Here is the structure of a PostgreSQL Anonymous Code Block:

DO $$
   — declare something
   — do something

Let’s look at our Anonymous Code Block while pointing out a few tips and tricks:

  • We are declaring multiple variables as we would in a PostgreSQL function and very similar to doing so in any query editor session.
  • We output our test values with the PostgreSQL RAISE command, in this case RAISE NOTICE.
  • We are printing values and can output multiple records as well. Multiple record output is accomplished by creating a temporary table which persists after the Anonymous Block executes.

Our Output

As we mentioned, we are printing values to the message window with RAISE NOTICE as well as outputting multiple records using a temporary table. Here’s our message window.

And the multiple records from our statement

select * from _x;

which is outside of the anonymous code block.