Postgres Pro
Downloads
Home   >   mailing lists

Thread: Rule for all the tables in a schema

Rule for all the tables in a schema

From
Sajeev Mayandi
Date:
Hi,

I am in the process of creating a rule  that provides upsert functionality for all the tables which has primary key.    The issue here is we have some 50 to 60 odd tables and have to write a functions that iterates through all these tables , create rules for each of this table, so that the rule body provides  upsert functionality for every one of them.

Is there a way, I can say create a rule for all the tables in an schema? This will avoid writing complicated functions.

Thanks,

Sajeev

Re: Rule for all the tables in a schema

From
Sergey Konoplev
Date:
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

Re: Rule for all the tables in a schema

From
Chris Travers
Date:



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

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

Re: Rule for all the tables in a schema

From
Sergey Konoplev
Date:
On Wed, May 22, 2013 at 11:49 PM, Chris Travers <chris.travers@gmail.com> wrote:
> For pre-9.0, just explicitly create, run, and drop a pl/pgsql function.
> Much easier than a shell script.

+1, good point.

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

Re: Rule for all the tables in a schema

From
Sajeev Mayandi
Date:
Thanks. I am using  9.2. So will use serge's method.

On 5/22/13 11:53 PM, "Sergey Konoplev" <gray.ru@gmail.com> wrote:

>On Wed, May 22, 2013 at 11:49 PM, Chris Travers <chris.travers@gmail.com>
>wrote:
>> For pre-9.0, just explicitly create, run, and drop a pl/pgsql function.
>> Much easier than a shell script.
>
>+1, good point.
>
>--
>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