Re: What is a DO block for?

On Thu, May 23, 2013 at 5:58 AM, Rob Richardson <RDRichardson(at)rad-con(dot)com>wrote:

> Greetings!
>
> Another post on this list suggested using a DO block if the user's
> Postgres version is 9.0 or later. The documentation for the DO block says
> what it is, but not what it is for. The only benefit I could see for it is
> allowing the use of locally defined variables. I'm sure there's more to it
> than that. What justifies the existence of the DO block?
>

A DO block allows you to run an anonymous stored procedure, defined in
place, in whatever procedural language you would like.

Basically it gives you some limited ad hoc access to procedural languages
that you can't get otherwise. The major limitation is that a DO block
can't return anything (which makes sense since it isn't really a planned
statement).

You could use it, for example, to process all rows in a table using Perl,
Python, or TCL without creating a function that could be re-used.

In this case the suggestion was to run DDL statements which are not
parameterized by assembling strings via an SQL query and running them. You
can't really do this in SQL because you have no way to turn the string into
another query, so the DO block lets you do this inside pl/pgsql where such
a facility does exist.

Hope this makes sense,
Chris Travers