Okay, thanks! -Mridul.
On Tue, Sep 27, 2011 at 12:14 AM, Guillaume Lelarge <[email protected]>wrote: > 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 > >
