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
