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).
Thanks in advance!
-Mridul.