Re: Rule for all the tables in a schema

On Wed, May 22, 2013 at 11:13 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> 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.
>

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