Monday, October 24, 2011

Some Object Information Functions improvements in PostgreSQL 9.1:

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)

No comments:

Post a Comment