Re: [GENERAL] Temporary tables and miscellaneous schemas

Bruce Momjian wrote:
> > As an FYI, I just updated to an Opteron box and have been enjoying a
> > little over 1500 temp schemas and a paltry ~30 non-temp schemas.
> > Getting this patch in would be oh so very appreciated as maintaining
> > local copies of psql(1) is getting old.  I know it's not my decision
> > to make, but I'd settle and shut up if there was an indirect proof for
> > why this shouldn't be included as a patch (ie, a valid usecase for an
> > admin or programmer who would need to see any or all of the pg_temp_*
> > schemas without using that data to extract more bits from the
> > pg_catalogs.  If they know how to go through the catalogs, why do they
> > need \dn to display the temp schemas?).
> 
> OK, the following patch uses UNION and an =ANY() join to the
> current_schemas() array to suppress non-local temp schemas, but display
> all other schemas.
> 
> There is now cleaner way to join to the current_schemas() array, right?

Sorry, here is the proper patch.  I had forgotten to call
processNamePattern() twice for the UNION.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.90
diff -c -c -r1.90 describe.c
*** src/bin/psql/describe.c	1 Dec 2003 22:21:54 -0000	1.90
--- src/bin/psql/describe.c	22 Dec 2003 07:11:30 -0000
***************
*** 1626,1639 ****
  
  	initPQExpBuffer(&buf);
  	printfPQExpBuffer(&buf,
! 					  "SELECT n.nspname AS \"%s\",\n"
! 					  "       u.usename AS \"%s\"\n"
  		"FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
! 					  "       ON n.nspowner=u.usesysid\n",
  					  _("Name"),
  					  _("Owner"));
  
! 	processNamePattern(&buf, pattern, false, false,
  					   NULL, "n.nspname", NULL,
  					   NULL);
  
--- 1626,1654 ----
  
  	initPQExpBuffer(&buf);
  	printfPQExpBuffer(&buf,
! 		"SELECT n.nspname AS \"%s\",\n"
! 		"       u.usename AS \"%s\"\n"
  		"FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
! 		"       ON n.nspowner=u.usesysid\n"
! 		"WHERE	n.nspname NOT LIKE 'pg_temp_%%'\n",
  					  _("Name"),
  					  _("Owner"));
+ 	processNamePattern(&buf, pattern, true, false,
+ 					   NULL, "n.nspname", NULL,
+ 					   NULL);
  
! 	appendPQExpBuffer(&buf,
! 		"UNION ALL\n"	/* show only local temp schema */
! 		"SELECT n.nspname AS \"%s\",\n"
! 		"       u.usename AS \"%s\"\n"
! 		"FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
! 		"       ON n.nspowner=u.usesysid,\n"
! 		"	  (SELECT current_schemas('t'::boolean)) AS curr_schemas(name)\n"
! 		"WHERE	n.nspname LIKE 'pg_temp_%%' AND\n"
! 		"		n.nspname = ANY(curr_schemas.name)\n",
! 					  _("Name"),
! 					  _("Owner"));
! 	processNamePattern(&buf, pattern, true, false,
  					   NULL, "n.nspname", NULL,
  					   NULL);