What is a DO block for?

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?

The message that mentioned the DO block is quoted below as an example.

Thanks very much!

RobR

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sergey Konoplev
Sent: Thursday, May 23, 2013 2:14 AM
To: Sajeev Mayandi
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Rule for all the tables in a schema

On Wed, May 22, 2013 at 10:34 PM, Sajeev Mayandi <Sajeev_Mayandi(at)symantec(dot)com> wrote:
> Is there a way, I can say create a rule for all the tables in an schema?
> This will avoid writing complicated functions.

You can use DO block if your postgres version is >=9.0.

DO $$
DECLARE _tablename text
BEGIN
FOR
SELECT INTO _tablename tablename
FROM pg_tables WHERE schemaname = 'schemaname'
LOOP
EXECUTE 'CREATE RULE ... TO $1 ...' USING _tablename;
END LOOP;
END $$;

For <9.0 you can use shell script with psql to do the same.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general