1. pg_describe_object:

In PostgreSQL 9.1, pg_describe_object function has been added. Using this function, user can get human readable string for understanding the pg_depend content as given below:

postgres=# select classid, objid, objsubid, pg_describe_object(classid, objid, objsubid) from pg_depend where classid <> 0 and pg_describe_object(classi
 classid | objid | objsubid |               pg_describe_object
---------+-------+----------+------------------------------------------------
    1247 | 16426 |        0 | type test_money
    1247 | 16425 |        0 | type test_money[]
    1259 | 16424 |        0 | table test_money
    1259 | 16429 |        0 | index test_money_idx
    2606 | 16431 |        0 | constraint uniq_constraint on table test_money

2. quote_all_identifiers (boolean) parameter:

With this parameter, User can force quoating of all identifiers in EXMPLAIN and in system catalog functions like pg_get_viewdef().
Example is given below:

postgres=# explain select * from emp;
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.14 rows=14 width=41)
(1 row)

postgres=# set quote_all_identifiers to true;
SET
postgres=# explain select * from emp;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on "emp"  (cost=0.00..1.14 rows=14 width=41)
(1 row)

Similarly for pg_get_viewdef()/any system function, example is given below:

postgres=# select pg_get_Viewdef('salesemp');
                                                  pg_get_viewdef
-------------------------------------------------------------------------------------------------------------------
 SELECT emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm FROM emp WHERE ((emp.job)::text = 'SALESMAN'::text);
(1 row)

postgres=# set quote_all_identifiers to true;
SET
postgres=# select pg_get_Viewdef('salesemp');
                                                                 pg_get_viewdef
-------------------------------------------------------------------------------------------------------------------------------------------------
 SELECT "emp"."empno", "emp"."ename", "emp"."hiredate", "emp"."sal", "emp"."comm" FROM "emp" WHERE (("emp"."job")::"text" = 'SALESMAN'::"text");
(1 row)

Cluster-wise setting of quote_all_identifiers,will force pg_dump/pg_dumpall command to quote identifiers while taking database dump

3. Complete implementation of columns about sequence in information_schema.sequences:

In Previous versions of PostgreSQL Colums about sequence existed in information_schema.sequence view, however sequenece parameter were not implemented.

Now, in 9.1, there is complete implementation of sequence parameter in information_schema.sequeunces. Example is given below:

In 9.0:

postgres=# \x
Expanded display is on.
postgres=# select * from information_schema.sequences where sequence_name='next_empno';
-[ RECORD 1 ]-----------+-----------
sequence_catalog        | postgres
sequence_schema         | public
sequence_name           | next_empno
data_type               | bigint
numeric_precision       | 64
numeric_precision_radix | 2
numeric_scale           | 0
maximum_value           |
minimum_value           |
increment               |
cycle_option            |

In 9.1:

postgres=# select * from information_schema.sequences where sequence_name='next_empno';
-[ RECORD 1 ]-----------+--------------------
sequence_catalog        | postgres
sequence_schema         | public
sequence_name           | next_empno
data_type               | bigint
numeric_precision       | 64
numeric_precision_radix | 2
numeric_scale           | 0
start_value             | 8000
minimum_value           | 1
maximum_value           | 9223372036854775807
increment               | 1
cycle_option            | NO


4. public as a pseudo-role name in has_table_privilege() and related functions:

In pervious verions, public role was not recognized by has_*_previleges. Now in 9.1, public as pseudo-role name has been added, which allows checking for public permissions.
Example is given below:
In PostgreSQL 9.0:

postgres=# select has_table_privilege('public','emp','select');
ERROR:  role "public" does not exist

In postgreSQL 9.1:

postgres=# select has_table_privilege('public','emp','select');
 has_table_privilege
---------------------
 t
(1 row)

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s