Re: 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