I've started playing with a structure based on the description in this message: http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&selm=1043257402.83856.112.camel%40jester&rnum=21
Basically, it consists of a very simple schema to tell PSQL what \? commands are available, and the query to use to fetch the information. - User types \d<command>. Psql (as a last resort after the checks in command.c) will query the database to see if the \d<command> exists with the number of arguments the user has supplied. - If it exists (regular expression match of what the user typed as <command> against column cmd_expression), it pulls out the SQL, prepares it via PREPARE, EXECUTES it, and displays the results. See schema and simple psql.patch attached. Caching, etc. could be done to speed things up -- not to mention keeping the plans around, but this was a quick hack. 2 sample commands are included. \dqtest (prints out a row of junk), \dqdb (prints db listing -- like \l), and \dqdb <arg> (prints db listing -- but with a LIKE match on the dbname). Thoughts or remarks? It allows all versions of psql using that table to pick up available commands for the database (old psql, new db gets commands for new db) but translations for column headers, etc. will be wonky as they're still tied to psql. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc
? .deps
? .describe.c.swp
? psql
Index: command.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/command.c,v
retrieving revision 1.88
diff -c -r1.88 command.c
*** command.c 2003/01/10 21:57:44 1.88
--- command.c 2003/01/25 01:43:38
***************
*** 392,398 ****
break;
default:
! status = CMD_UNKNOWN;
}
if (pattern)
--- 392,401 ----
break;
default:
! success = describeUnmatched(cmd, pattern);
!
! if (!success)
! status = CMD_UNKNOWN;
}
if (pattern)
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.74
diff -c -r1.74 describe.c
*** describe.c 2003/01/07 20:56:06 1.74
--- describe.c 2003/01/25 01:44:09
***************
*** 54,60 ****
--- 54,165 ----
return tmp;
}
+ /*
+ * Checks the database for instructions on how to deal with any unmatched commands
+ *
+ * Returns true if it found and successfully processed the command.
+ */
+ bool
+ describeUnmatched(const char *cmd, const char *pattern)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ char esccmd[strlen(cmd) * 2 + 1];
+ char escpattern[strlen(cmd) * 2 + 1];
+ int nargs = (pattern ? 1 : 0);
+ char *fTitle;
+ char *fTabQuery;
+ char *fInfoQuery;
+
+ /* Clean up the input data */
+ PQescapeString(esccmd, cmd, strlen(cmd));
+
+ if (pattern)
+ PQescapeString(escpattern, pattern, strlen(pattern));
+
+ /* Query the DB to see if there is a command matching the request */
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT table_title, table_query, info_query
+"
+ "FROM pgtools.psqlcommands "
+ "WHERE nargs = '%d' AND '%s' ~
+cmd_expression "
+ "ORDER BY match_order LIMIT 1",
+ nargs, cmd
+ );
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ if (!PQntuples(res))
+ return false;
+
+ fTitle = PQgetvalue(res, 0, 0);
+ fTabQuery = PQgetvalue(res, 0, 1);
+ fInfoQuery = PQgetvalue(res, 0, 2);
+
+ PQclear(res);
+
+ /* Prepare queries */
+ printfPQExpBuffer(&buf, "PREPARE pg_psql ");
+
+ if (nargs > 0)
+ {
+ int i;
+ appendPQExpBuffer(&buf, "(");
+
+ for (i = 0; i < nargs - 1; i++)
+ appendPQExpBuffer(&buf, "text,");
+
+ appendPQExpBuffer(&buf, "text)");
+ }
+ appendPQExpBuffer(&buf, "AS %s", fTabQuery);
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ /* Run queries, with arguments (cmd / pattern) */
+ printfPQExpBuffer(&buf, "EXECUTE pg_psql");
+
+ if (nargs > 0)
+ {
+ int i;
+ appendPQExpBuffer(&buf, "(");
+
+ for (i = 0; i < nargs - 1; i++)
+ appendPQExpBuffer(&buf, "'dummy',");
+
+ appendPQExpBuffer(&buf, "'%s')", escpattern);
+ }
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = gettext(fTitle);
+
+ printQuery(res, &myopt, pset.queryFout);
+
+ /* Clean-up our prepared query */
+ printfPQExpBuffer(&buf, "DEALLOCATE pg_psql");
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ PQclear(res);
+ return true;
+ }
+
/*----------------
* Handlers for various slash commands displaying some sort of list
* of things in the database.
***************
*** 62,68 ****
* If you add something here, try to format the query to look nice in -E output.
*----------------
*/
-
/* \da
* Takes an optional regexp to select particular aggregates
--- 167,172 ----
Index: describe.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.h,v
retrieving revision 1.20
diff -c -r1.20 describe.h
*** describe.h 2003/01/07 20:56:07 1.20
--- describe.h 2003/01/25 01:44:09
***************
*** 52,56 ****
--- 52,58 ----
/* \dn */
bool listSchemas(const char *pattern);
+ /* Try all unmatched commands against this */
+ bool describeUnmatched(const char *cmd, const char *pattern);
#endif /* DESCRIBE_H */
DROP SCHEMA pgtools CASCADE;
BEGIN;
CREATE SCHEMA pgtools
CREATE TABLE psqlcommands
( match_order serial primary key
, nargs integer not null
, cmd_expression varchar(50) not null
, table_title varchar(60) not null
, table_query text not null
, info_query text
)
;
-- \dqtest
INSERT INTO pgtools.psqlcommands
VALUES (DEFAULT, 0, 'dqtest', 'WOO HOO!',
'SELECT 3, 4', NULL);
-- \dqdb
INSERT INTO pgtools.psqlcommands
VALUES (DEFAULT, 0, 'dqdb', 'List Databases',
'SELECT d.datname AS "Name",
u.usename AS "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database AS d
LEFT JOIN pg_catalog.pg_user AS U ON (d.datdba = u.usesysid)
ORDER BY "Name"', NULL);
-- \dqdb <DBName>
INSERT INTO pgtools.psqlcommands
VALUES (DEFAULT, 1, 'dqdb', 'List Databases',
'SELECT d.datname AS "Name",
u.usename AS "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database AS d
LEFT JOIN pg_catalog.pg_user AS U ON (d.datdba = u.usesysid)
WHERE d.datname LIKE ''%'' || $1 || ''%''
ORDER BY "Name"', NULL);
COMMIT;
select * from pgtools.psqlcommands;
signature.asc
Description: This is a digitally signed message part
