On Mon, 2011-09-26 at 16:06 +0530, Mridul Mathew wrote: > Hello, > > I wanted to take a moment here and reach out to the community with a > question. How does postgreSQL handle schema switching in the database? I > have a test scenario. > > ################## > > vpn2=> show search_path; > search_path > ------------- > mridul > (1 row) > > CREATE OR REPLACE PACKAGE mridul.TEST_PKG > IS > PROCEDURE execute_for_all_schema(); > end TEST_PKG; > > CREATE OR REPLACE PACKAGE BODY mridul.TEST_PKG > IS > > PROCEDURE execute_for_all_schema() IS > > CURSOR c_schema_names IS > SELECT name > FROM mridul.schema_config > WHERE status = 'ACTIVE'; > > BEGIN > > FOR dat_schema_names IN c_schema_names LOOP > ----- EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = '|| > dat_schema_names.name; > EXECUTE IMMEDIATE 'SET SEARCH_PATH = '|| dat_schema_names.name; > EXECUTE IMMEDIATE 'insert into A values (1)'; > commit; > END LOOP; > EXCEPTION > WHEN OTHERS THEN > RAISE; > > END; > end TEST_PKG; > > vpn2=> select * from mridul.schema_config; > id | name | description | status | last_device_sync_date > ----+-------+-------------+--------+----------------------- > 1 | TEST | TESTING | ACTIVE | 22-SEP-11 18:28:11 > 6 | TEST1 | TESTING5 | ACTIVE | 22-SEP-11 11:46:09 > 2 | TEST2 | TESTING2 | ACTIVE | 23-SEP-11 11:54:35 > (3 rows) > > vpn2=> show search_path; > search_path > ------------- > mridul > (1 row) > > vpn2=> select count(*) from test.a; > count > ------- > 0 > (1 row) > > > vpn2=> select count(*) from test1.a; > count > ------- > 0 > (1 row) > > > vpn2=> select count(*) from test2.a; > count > ------- > 0 > (1 row) > > vpn2=> exec mridul.TEST_PKG.execute_for_all_schema; > > EDB-SPL Procedure successfully completed > vpn2=> select count(*) from test.a; > count > ------- > 1 > (1 row) > > > vpn2=> select count(*) from test1.a; > count > ------- > 1 > (1 row) > > > vpn2=> select count(*) from test2.a; > count > ------- > 1 > (1 row) > > If I convert the 'insert into A ...' part from a dynamic dml to a normal > insert, and then execute the procedure, there are 3 rows inserted into > test.a and none go into test1.a nor test2.a. However, if I echo the > search_path after executing the proc, it displays the last schema's name > (meaning, it has switched the schemas, but the insert has gone into only the > first schema). > This isn't a PostgreSQL question, this is an EnterpriseDB one. Packages are not part of the community PostgreSQL distribution. "EXECUTE IMMEDIATE" is also not part of the community PostgreSQL distribution. So I guess you need to ask them. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
