On Wed, May 22, 2013 at 11:13 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
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.
For pre-9.0, just explicitly create, run, and drop a pl/pgsql function. Much easier than a shell script.
Best Wishes,
Chris Travers
-- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA
