I find the do block a nice enhancement; for example, it allows me to do many administration tasks quickly without adding a procedure to the database. Imagine that I need to truncate all the tables in a schema for development purposes in order to fill it with test data. I could do like this
DO $$
DECLARE
table_name text;
BEGIN
FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname = 'development' LOOP
EXECUTE 'TRUNCATE ' || table_name ||' CASCADE ;' ;
END LOOP;
END;
$$;
From: Chris Travers <chris.travers@xxxxxxxxx>
To: Rob Richardson <RDRichardson@xxxxxxxxxxx>
Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, May 23, 2013 3:04 PM
Subject: Re: What is a DO block for?
DO $$
DECLARE
table_name text;
BEGIN
FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname = 'development' LOOP
EXECUTE 'TRUNCATE ' || table_name ||' CASCADE ;' ;
END LOOP;
END;
$$;
Regards
From: Chris Travers <chris.travers@xxxxxxxxx>
To: Rob Richardson <RDRichardson@xxxxxxxxxxx>
Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, May 23, 2013 3:04 PM
Subject: Re: What is a DO block for?
On Thu, May 23, 2013 at 5:58 AM, Rob Richardson <RDRichardson@xxxxxxxxxxx> 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
