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:
- How to create local variables in PostgreSQL
- 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:
— 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.
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.