Re: PostgreSQL question

Okay, thanks!

-Mridul.

On Tue, Sep 27, 2011 at 12:14 AM, Guillaume Lelarge
<guillaume(at)lelarge(dot)info>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
>
>