Rule for all the tables in a schema
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
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
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
Show quoted text
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBAProfile: 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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 DBAProfile: 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