-----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
