Following discussion 
(http://archives.postgresql.org/pgsql-hackers/2006-03/msg01208.php) here's a 
patch to add tablespace to the output of psql's \l+. New output (with psql 
-E) looks like this:

pyarra=# \l+
********* QUERY **********
SELECT d.datname as "Name",
       r.rolname as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description",
       t.spcname as "Tablespace"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
**************************

                           List of databases
   Name    | Owner  | Encoding |        Description        | Tablespace
-----------+--------+----------+---------------------------+------------
 postgres  | pyarra | LATIN1   |                           | pg_default
 pyarra    | pyarra | LATIN1   |                           | pg_default
 spctest   | pyarra | LATIN1   |                           | spctable
 template0 | pyarra | LATIN1   |                           | pg_default
 template1 | pyarra | LATIN1   | Default template database | pg_default
(5 rows)

Please let me know if this patch needs any changes for acceptance. I would 
prefer the tablespace row to the left of Description, but opted for minimal 
change to get patch accepted. Will re-order them if others are in favour.

Regards, Philip.

-----------------
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.

Index: src/bin/psql/describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.133
diff -c -r1.133 describe.c
*** src/bin/psql/describe.c	5 Mar 2006 15:58:51 -0000	1.133
--- src/bin/psql/describe.c	30 Mar 2006 00:23:57 -0000
***************
*** 362,375 ****
  			",\n       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\"",
  					  _("Encoding"));
  	if (verbose)
  		appendPQExpBuffer(&buf,
  						  ",\n       pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
  						  _("Description"));
  	appendPQExpBuffer(&buf,
  					  "\nFROM pg_catalog.pg_database d"
! 				  "\n  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n"
! 					  "ORDER BY 1;");
! 
  	res = PSQLexec(buf.data, false);
  	termPQExpBuffer(&buf);
  	if (!res)
--- 362,382 ----
  			",\n       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\"",
  					  _("Encoding"));
  	if (verbose)
+ 	{
  		appendPQExpBuffer(&buf,
  						  ",\n       pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
  						  _("Description"));
+ 		appendPQExpBuffer(&buf,
+ 						  ",\n       t.spcname as \"%s\"",
+ 						  _("Tablespace"));
+ 	}
  	appendPQExpBuffer(&buf,
  					  "\nFROM pg_catalog.pg_database d"
! 				  "\n  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n");
! 	if (verbose)
! 		appendPQExpBuffer(&buf,
! 					"  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");	
! 	appendPQExpBuffer(&buf,"ORDER BY 1;");
  	res = PSQLexec(buf.data, false);
  	termPQExpBuffer(&buf);
  	if (!res)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to