How to change all objects ownership in a particular schema in PostgreSQL ?

Few suggesion's here (Thanks), inspired me to compose a bash script for changing all object's (TABLES / SEQUENCES / VIEWS / FUNCTIONS / AGGREGATES / TYPES) ownership in a particular schema in one go. No special code included in a script, I basically picked the technique suggested and simplified the implementation method via script. Actually, REASSIGN OWNED BY command does most of the work smoothly, however, it changes database-wide objects ownership regardless of any schema. Two eventualities, where you may not use REASSIGN OWNED BY:

1. If the user by mistake creates all his objects with super-user(postgres), and later intend to change to other user, then REASSIGN OWNED BY will not work and it merely error out as:

postgres=# reassign owned by postgres to user1;
ERROR:  cannot reassign ownership of objects owned by role postgres because they are required by the database system

2. If user wish to change just only one schema objects ownership.

Either cases of changing objects, from "postgres" user to other user or just changing only one schema objects, we need to loop through each object by collecting object details from pg_catalog's & information_schema and calling ALTER TABLE / FUNCTION / AGGREGATE / TYPE etc.

I liked the technique of tweaking pg_dump output using OS commands(sed/egrep), because it known that by nature the pg_dump writes ALTER .. OWNER TO of every object (TABLES / SEQUENCES / VIEWS / FUNCTIONS / AGGREGATES / TYPES) in its output. Grep'ing those statements from pg_dump stdout by replacing new USER/SCHEMANAME with sed and then passing back those statements to psql client will fix the things even if the object owned by Postgres user. I used same approach in script and allowed user to pass NEW USER NAME and SCHEMA NAME, so to replace it in ALTER...OWNER TO.. statement.

Script usage and output:

sh change_owner.sh  -n new_rolename -S schema_name

-bash-4.1$ sh change_owner.sh -n user1 -S public

 Summary:
        Tables/Sequences/Views : 16
        Functions              : 43
        Aggregates             : 1
        Type                   : 2

You can download the script from here, and there's also README to help you on the usage.

--Raghav

New Oracle-Compatibility features in PostgresPlus Advanced Server 9.3Beta

In short about EnterpriseDB product PostgresPlus Advanced Server, its built upon PostgreSQL community version with Oracle-Compatibility features and its ability to run with existing Oracle applications without any re-coding(typically required in migration) and allow enterprises to enjoy the benefit of the Open source database with PostgresPlus Advanced Server.

As PostgresPlus Advanced Server 9.3 Beta built upon community PostgreSQL 9.3 Beta feature, thence varied Oracle-Compatiblity features introduced in BETA unleash, like Oracle-style syntaxes, packages, SQL function etc. Below are few of the examples executed in PPAS 9.3 BETA:-

Materialized Views Syntax:

CREATE MATERIALIZED VIEW name [build_clause][create_mv_refresh] AS subquery
Where build_clause is:
BUILD {IMMEDIATE | DEFERRED}
Where create_mv_refresh is:
REFRESH [COMPLETE] [ON DEMAND]

• Build deferred - In this option, data not populated in MV at the creation time, instead its populate later by using REFRESH MATERIALIZED VIEW.
• Build Immediate - In this option, data populated in MV at the creation time from tables (Default)

edb=# create materialized view mymview1 
build immediate 
as select ename,sum(sal) from emp group by ename; 
SELECT 14 
edb=# create materialized view mymview2 
build deferred 
as select ename,sum(sal) from emp group by ename; 
SELECT 0

As you'll be able to figure it out from above output that "BUILD IMMEDIATE" populates all rows at the create time, whereas "BUILD DEFERRED" simply created a dummy object that ought to be later populated using REFRESH MATERIALIZED VIEW.

edb=# refresh materialized view mymview2 ;
REFRESH MATERIALIZED VIEW
edb=# select count(*) from mymview2;
 count
-------
    14
(1 row)

REFRESH [COMPLETE] [ON DEMAND] modes are also supported at creation time of Materialized View.

More Oracle SQL functions support:
REGEXP_SUBSTR()

edb=# SELECT REGEXP_SUBSTR('EDB documentation AT http://www.enterprisedb.com/','http://([[:alnum:]]+){3,4}/?') as RESULT FROM dual;
   result
------------
 http://www
(1 row)

REGEXP_COUNT()

edb=# select regexp_count('I am in PG-Mailing list','i',1,'i');
 regexp_count
--------------
            5
(1 row)

REGEXP_INSTR()

edb=# SELECT REGEXP_INSTR('PostgreSQL','P') "position" FROM dual;
 position
----------
        1
(1 row)

RAWTOHEX() / HEXTORAW ()

edb=# CREATE TABLE test (raw_col RAW(10));
CREATE TABLE
edb=# INSERT INTO test VALUES (HEXTORAW('7D'));
INSERT 0 1
edb=# select * from test ;
 raw_col
---------
 \x7d
(1 row)

edb=# select rawtohex(raw_col) from test ;
 rawtohex
----------
 7d
(1 row)

More Oracle Packages support:

DBMS_SCHEDULER
DBMS_RANDOM
DBMS_CRYPTO
DBMS_MVIEW
DBMS_LOCK.sleep
UTL_HTTP
UTL_ENCODE
UTL_URL

For more details you can refer release notes:
http://get.enterprisedb.com/releasenotes/PgPlus_AS_93Beta1_Release_Notes_20130802.pdf

--Raghav