Thread: 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
forthe 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
locallydefined 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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sergey Konoplev
Sent: Thursday, May 23, 2013 2:14 AM
To: Sajeev Mayandi
Cc: pgsql-general@postgresql.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@symantec.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.ru@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, May 23, 2013 at 5:58 AM, Rob Richardson <RDRichardson@rad-con.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
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@gmail.com>
To: Rob Richardson <RDRichardson@rad-con.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Thursday, May 23, 2013 3:04 PM
Subject: Re: [GENERAL] 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@gmail.com>
To: Rob Richardson <RDRichardson@rad-con.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Thursday, May 23, 2013 3:04 PM
Subject: Re: [GENERAL] What is a DO block for?
On Thu, May 23, 2013 at 5:58 AM, Rob Richardson <RDRichardson@rad-con.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
On 05/23/2013 05:58 AM, Rob Richardson wrote: > > Greetings! > > Another post on this list suggested using a DO block if the user's Postgres version is 9.0 or later. The documentationfor the DO block says what it is, but not what it is for. The only benefit I could see for it is allowing theuse 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. In database processing without having to create a function that is stored. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats