-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
In response to Bruce's recent question, and remembering the recent
scolding received about removing a "LEFT" from an unrelated patch :),
here's a patch that does that and nothing more: removes all
unnecessary left joins from psql. Done in the theory that we often
point people to psql -E, so our queries there should be as correct
as possible: if the joining column is not null, then we don't need
a left join.
 
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200504112254
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFCWzjVvJuQZxSWSsgRAkwLAKDRMAV3GK/mYEMYEkP4SGC6CWqGNgCg1yIf
oQf+YvYkHJRK1WDz11Hw1Oc=
=oZDp
-----END PGP SIGNATURE-----

Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.115
diff -c -r1.115 describe.c
*** describe.c	6 Apr 2005 05:23:32 -0000	1.115
--- describe.c	11 Apr 2005 15:59:11 -0000
***************
*** 75,81 ****
  					  "  END AS \"%s\",\n"
  			 "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
  					  "FROM pg_catalog.pg_proc p\n"
! 	"     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  					  "WHERE p.proisagg\n",
  					  _("Schema"), _("Name"), _("(all types)"),
  					  _("Data type"), _("Description"));
--- 75,81 ----
  					  "  END AS \"%s\",\n"
  			 "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
  					  "FROM pg_catalog.pg_proc p\n"
! 	"     JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  					  "WHERE p.proisagg\n",
  					  _("Schema"), _("Name"), _("(all types)"),
  					  _("Data type"), _("Description"));
***************
*** 187,199 ****
  	if (!verbose)
  		appendPQExpBuffer(&buf,
  						  "\nFROM pg_catalog.pg_proc p"
! 						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
  	else
  		appendPQExpBuffer(&buf,
  						  "\nFROM pg_catalog.pg_proc p"
! 						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
! 		 "\n     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
! 						  "\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner\n");
  
  	/*
  	 * we skip in/out funcs by excluding functions that take or return
--- 187,199 ----
  	if (!verbose)
  		appendPQExpBuffer(&buf,
  						  "\nFROM pg_catalog.pg_proc p"
! 						  "\n     JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
  	else
  		appendPQExpBuffer(&buf,
  						  "\nFROM pg_catalog.pg_proc p"
! 						  "\n     JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
! 		 "\n     JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
! 						  "\n     JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner\n");
  
  	/*
  	 * we skip in/out funcs by excluding functions that take or return
***************
*** 259,265 ****
  					  _("Description"));
  
  	appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
! 					  "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
  
  	/*
  	 * do not include array types (start with underscore); do not include
--- 259,265 ----
  					  _("Description"));
  
  	appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
! 					  "     JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
  
  	/*
  	 * do not include array types (start with underscore); do not include
***************
*** 315,321 ****
  		 "  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
  					  "           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
  					  "FROM pg_catalog.pg_operator o\n"
! 					  "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
  					  _("Schema"), _("Name"),
  					  _("Left arg type"), _("Right arg type"),
  					  _("Result type"), _("Description"));
--- 315,321 ----
  		 "  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
  					  "           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
  					  "FROM pg_catalog.pg_operator o\n"
! 					  "     JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
  					  _("Schema"), _("Name"),
  					  _("Left arg type"), _("Right arg type"),
  					  _("Result type"), _("Description"));
***************
*** 368,374 ****
  						  _("Description"));
  	appendPQExpBuffer(&buf,
  					  "\nFROM pg_catalog.pg_database d"
! 		  "\n  LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid\n"
  					  "ORDER BY 1;");
  
  	res = PSQLexec(buf.data, false);
--- 368,374 ----
  						  _("Description"));
  	appendPQExpBuffer(&buf,
  					  "\nFROM pg_catalog.pg_database d"
! 		  "\n  JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid\n"
  					  "ORDER BY 1;");
  
  	res = PSQLexec(buf.data, false);
***************
*** 409,415 ****
  					  "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
  					  "  c.relacl as \"%s\"\n"
  					  "FROM pg_catalog.pg_class c\n"
! 	"     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
  					  "WHERE c.relkind IN ('r', 'v', 'S')\n",
  					  _("Schema"), _("Name"), _("table"), _("view"), _("sequence"), _("Type"), _("Access privileges"));
  
--- 409,415 ----
  					  "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
  					  "  c.relacl as \"%s\"\n"
  					  "FROM pg_catalog.pg_class c\n"
! 	"     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
  					  "WHERE c.relkind IN ('r', 'v', 'S')\n",
  					  _("Schema"), _("Name"), _("table"), _("view"), _("sequence"), _("Type"), _("Access privileges"));
  
***************
*** 474,480 ****
  					  "  CAST(p.proname AS pg_catalog.text) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_proc p\n"
! 					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  					  "  WHERE p.proisagg\n",
  					  _("aggregate"));
  	processNamePattern(&buf, pattern, true, false,
--- 474,480 ----
  					  "  CAST(p.proname AS pg_catalog.text) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_proc p\n"
! 					  "       JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  					  "  WHERE p.proisagg\n",
  					  _("aggregate"));
  	processNamePattern(&buf, pattern, true, false,
***************
*** 489,495 ****
  					  "  CAST(p.proname AS pg_catalog.text) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_proc p\n"
! 					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  
  	 "  WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
  					  "      AND (p.proargtypes[0] IS NULL\n"
--- 489,495 ----
  					  "  CAST(p.proname AS pg_catalog.text) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_proc p\n"
! 					  "       JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  
  	 "  WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
  					  "      AND (p.proargtypes[0] IS NULL\n"
***************
*** 508,514 ****
  					  "  CAST(o.oprname AS pg_catalog.text) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_operator o\n"
! 					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
  					  _("operator"));
  	processNamePattern(&buf, pattern, false, false,
  					   "n.nspname", "o.oprname", NULL,
--- 508,514 ----
  					  "  CAST(o.oprname AS pg_catalog.text) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_operator o\n"
! 					  "       JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
  					  _("operator"));
  	processNamePattern(&buf, pattern, false, false,
  					   "n.nspname", "o.oprname", NULL,
***************
*** 522,528 ****
  					  "  pg_catalog.format_type(t.oid, NULL) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_type t\n"
! 					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
  					  _("data type"));
  	processNamePattern(&buf, pattern, false, false,
  				"n.nspname", "pg_catalog.format_type(t.oid, NULL)", NULL,
--- 522,528 ----
  					  "  pg_catalog.format_type(t.oid, NULL) as name,"
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_type t\n"
! 					  "       JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
  					  _("data type"));
  	processNamePattern(&buf, pattern, false, false,
  				"n.nspname", "pg_catalog.format_type(t.oid, NULL)", NULL,
***************
*** 538,544 ****
  					  "    CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
  					  "  AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_class c\n"
! 					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
  					  "  WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
  					  _("table"), _("view"), _("index"), _("sequence"));
  	processNamePattern(&buf, pattern, true, false,
--- 538,544 ----
  					  "    CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
  					  "  AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_class c\n"
! 					  "       JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
  					  "  WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
  					  _("table"), _("view"), _("index"), _("sequence"));
  	processNamePattern(&buf, pattern, true, false,
***************
*** 554,560 ****
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_rewrite r\n"
  			  "       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
! 					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
  					  "  WHERE r.rulename != '_RETURN'\n",
  					  _("rule"));
  	/* XXX not sure what to do about visibility rule here? */
--- 554,560 ----
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_rewrite r\n"
  			  "       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
! 					  "       JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
  					  "  WHERE r.rulename != '_RETURN'\n",
  					  _("rule"));
  	/* XXX not sure what to do about visibility rule here? */
***************
*** 571,577 ****
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_trigger t\n"
  			   "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
! 					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
  					  _("trigger"));
  	/* XXX not sure what to do about visibility rule here? */
  	processNamePattern(&buf, pattern, false, false,
--- 571,577 ----
  					  "  CAST('%s' AS pg_catalog.text) as object\n"
  					  "  FROM pg_catalog.pg_trigger t\n"
  			   "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
! 					  "       JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
  					  _("trigger"));
  	/* XXX not sure what to do about visibility rule here? */
  	processNamePattern(&buf, pattern, false, false,
***************
*** 622,628 ****
  					  "  n.nspname,\n"
  					  "  c.relname\n"
  					  "FROM pg_catalog.pg_class c\n"
! 					  "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
  
  	processNamePattern(&buf, pattern, false, false,
  					   "n.nspname", "c.relname", NULL,
--- 622,628 ----
  					  "  n.nspname,\n"
  					  "  c.relname\n"
  					  "FROM pg_catalog.pg_class c\n"
! 					  "     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
  
  	processNamePattern(&buf, pattern, false, false,
  					   "n.nspname", "c.relname", NULL,
***************
*** 1493,1505 ****
  						  "\nFROM pg_catalog.pg_class c"
  			  "\n     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
  			  "\n     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid"
! 		"\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
! 						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
  	else
  		appendPQExpBuffer(&buf,
  						  "\nFROM pg_catalog.pg_class c"
! 		"\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
! 						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
  
  	appendPQExpBuffer(&buf, "WHERE c.relkind IN (");
  	if (showTables)
--- 1493,1505 ----
  						  "\nFROM pg_catalog.pg_class c"
  			  "\n     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
  			  "\n     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid"
! 		"\n     JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
! 						  "\n     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
  	else
  		appendPQExpBuffer(&buf,
  						  "\nFROM pg_catalog.pg_class c"
! 		"\n     JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
! 						  "\n     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
  
  	appendPQExpBuffer(&buf, "WHERE c.relkind IN (");
  	if (showTables)
***************
*** 1582,1588 ****
  					  "       END as \"%s\",\n"
  						"       pg_catalog.pg_get_constraintdef(r.oid, true) as \"%s\"\n"
  					  "FROM pg_catalog.pg_type t\n"
! 	"     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
  	"     LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid\n"
  					  "WHERE t.typtype = 'd'\n",
  					  _("Schema"),
--- 1582,1588 ----
  					  "       END as \"%s\",\n"
  						"       pg_catalog.pg_get_constraintdef(r.oid, true) as \"%s\"\n"
  					  "FROM pg_catalog.pg_type t\n"
! 	"     JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
  	"     LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid\n"
  					  "WHERE t.typtype = 'd'\n",
  					  _("Schema"),
***************
*** 1737,1743 ****
  						  _("Access privileges"), _("Description"));
  
  	appendPQExpBuffer(&buf,
! 	  "\nFROM 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\\\\_%%' OR\n"
  	   "		 n.nspname = (pg_catalog.current_schemas(true))[1])\n");		/* temp schema is first */
--- 1737,1743 ----
  						  _("Access privileges"), _("Description"));
  
  	appendPQExpBuffer(&buf,
! 	  "\nFROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_user u\n"
  					  "       ON n.nspowner=u.usesysid\n"
  				 "WHERE	(n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
  	   "		 n.nspname = (pg_catalog.current_schemas(true))[1])\n");		/* temp schema is first */
---------------------------(end of broadcast)---------------------------
TIP 3: 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