On Mon, Apr 13, 2009 at 07:24:31PM -0400, Tom Lane wrote:
> David Fetter <[email protected]> writes:
> > Here's a patch that adds a "Function Type" column to \df while
> > removing the now-redundant \da.
>
> Removing \da altogether was nowhere in the consensus, or even in the
> discussion AFAIR.
It's back.
> Also, what is the point of using single-letter type codes when
> you've made the column header about as verbose as it could get? I'd
> go for something like
>
> Type
>
> window
> agg
> trigger
> normal
>
> Or we could spell out "aggregate", but that makes the column a
> couple of characters wider ...
Done.
I've also added \df[antw], which lets people narrow their search.
Cheers,
David.
--
David Fetter <[email protected]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [email protected]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 10d42ca..272f19b 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1043,14 +1043,15 @@ testdb=>
<listitem>
<para>
Lists available functions, together with their argument and
- return types. If <replaceable
- class="parameter">pattern</replaceable>
- is specified, only functions whose names match the pattern are shown.
- If the form <literal>\df+</literal> is used, additional information
about
- each function, including volatility, language, source code and
description, is shown.
- By default, only user-created objects are shown; supply a
- pattern or the <literal>S</literal> modifier to include system
- objects.
+ return types and their function type: 'normal', 'agg',
+ 'trigger', and 'window'. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ functions whose names match the pattern are shown. If the
+ form <literal>\df+</literal> is used, additional information
+ about each function, including volatility, language, source
+ code and description, is shown. By default, only user-created
+ objects are shown; supply a pattern or the
+ <literal>S</literal> modifier to include system objects.
</para>
<note>
@@ -1064,6 +1065,81 @@ testdb=>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\dfa[S+] [ <replaceable
class="parameter">pattern</replaceable> ]</literal></term>
+
+ <listitem>
+ <para>
+ Lists available aggregate functions, together with their argument and
+ return types. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ aggregate functions whose names match the pattern are shown.
+ If the form <literal>\dfa+</literal> is used, additional
+ information about each function, including volatility,
+ language, source code and description, is shown. By default,
+ only user-created objects are shown; supply a pattern or the
+ <literal>S</literal> modifier to include system objects.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>\dfn[S+] [ <replaceable
class="parameter">pattern</replaceable> ]</literal></term>
+
+ <listitem>
+ <para>
+ Lists available normal functions, together with their argument
+ and return types. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ normal functions whose names match the pattern are shown.
+ If the form <literal>\dfn+</literal> is used, additional
+ information about each function, including volatility,
+ language, source code and description, is shown. By default,
+ only user-created objects are shown; supply a pattern or the
+ <literal>S</literal> modifier to include system objects.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>\dft[S+] [ <replaceable
class="parameter">pattern</replaceable> ]</literal></term>
+
+ <listitem>
+ <para>
+ Lists available trigger functions, together with their argument and
+ return types. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ trigger functions whose names match the pattern are shown.
+ If the form <literal>\dft+</literal> is used, additional
+ information about each function, including volatility,
+ language, source code and description, is shown. By default,
+ only user-created objects are shown; supply a pattern or the
+ <literal>S</literal> modifier to include system objects.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>\dfw[S+] [ <replaceable
class="parameter">pattern</replaceable> ]</literal></term>
+
+ <listitem>
+ <para> Lists available windowing functions, together with
+ their argument and return types. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ windowing functions whose names match the pattern are shown.
+ If the form <literal>\dfw+</literal> is used, additional
+ information about each function, including volatility,
+ language, source code and description, is shown. By default,
+ only user-created objects are shown; supply a pattern or the
+ <literal>S</literal> modifier to include system objects.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>\dF[+] [ <replaceable
class="parameter">pattern</replaceable> ]</literal></term>
diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml
index fc56c3d..6802590 100644
--- a/doc/src/sgml/release.sgml
+++ b/doc/src/sgml/release.sgml
@@ -183,6 +183,16 @@ do it for earlier branch release files.
</para>
</listitem>
+ <listitem>
+ <para>
+ In psql, \df now shows which type of function it is: 'n' for
+ normal, 'a' for aggregate, 't' for trigger, and 'w' for
+ windowing. You can also get aggregates, etc. by \dfaS+, for
+ example. For now, you'd only get to choose one of a, n, t, w at
+ a time.
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b39466d..27da2de 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -365,8 +365,24 @@ exec_command(const char *cmd,
case 'D':
success = listDomains(pattern, show_system);
break;
- case 'f':
- success = describeFunctions(pattern,
show_verbose, show_system);
+ case 'f': /* function subsystem */
+ switch (cmd[2])
+ {
+ case '\0':
+ case '+':
+ case 'S':
+ success =
describeFunctions(pattern, show_verbose, show_system, 0);
+ break;
+ case 'a':
+ case 'n':
+ case 't':
+ case 'w':
+ success =
describeFunctions(pattern, show_verbose, show_system, cmd[2]);
+ break;
+ default:
+ status = PSQL_CMD_UNKNOWN;
+ break;
+ }
break;
case 'g':
/* no longer distinct from \du */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 731baf8..158bcfa 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -186,12 +186,35 @@ describeTablespaces(const char *pattern, bool verbose)
* Takes an optional regexp to select particular functions
*/
bool
-describeFunctions(const char *pattern, bool verbose, bool showSystem)
+describeFunctions(const char *pattern, bool verbose, bool showSystem, const
char function_type)
{
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
+ switch (function_type)
+ {
+ case 0:
+ case 'a':
+ case 'n':
+ case 't':
+ break;
+ case 'w':
+ if (pset.sversion < 80400)
+ {
+ fprintf(stderr, _("\\df does not take a \"w\"
decorator in %d.%d.\n"),
+ pset.sversion / 10000,
(pset.sversion / 100) % 100);
+ return true;
+ }
+ break;
+ default:
+ {
+ fprintf(stderr, _("\\df only takes empty, a, n,
t or w decorators.\n"));
+ return true;
+ }
+ break;
+ }
+
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
@@ -201,12 +224,33 @@ describeFunctions(const char *pattern, bool verbose, bool
showSystem)
gettext_noop("Name"));
if (pset.sversion >= 80400)
+ {
appendPQExpBuffer(&buf,
"
pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
"
pg_catalog.pg_get_function_arguments(p.oid) as \"%s\"",
gettext_noop("Result data
type"),
gettext_noop("Argument data
types"));
+ if (function_type == 0)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n"
+ " CASE\n"
+ " WHEN p.proisagg
THEN 'agg'\n"
+ " WHEN p.proiswindow
THEN 'window'\n"
+ " WHEN
pg_catalog.pg_get_function_result(p.oid) = 'trigger' THEN 't'\n"
+ " ELSE 'normal'\n"
+ "END as \"%s\"",
+
gettext_noop("Function Type"));
+ }
+ else
+ {
+ appendPQExpBuffer(&buf,
+ "\n /* We're in
\"%c\" mode. */\n",
+ function_type);
+ }
+ }
else if (pset.sversion >= 80100)
+ {
appendPQExpBuffer(&buf,
" CASE WHEN p.proretset THEN 'SETOF
' ELSE '' END ||\n"
" pg_catalog.format_type(p.prorettype, NULL)
as \"%s\",\n"
@@ -241,13 +285,50 @@ describeFunctions(const char *pattern, bool verbose, bool
showSystem)
" END AS \"%s\"",
gettext_noop("Result data
type"),
gettext_noop("Argument data
types"));
+ if (function_type == 0)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n"
+ " CASE\n"
+ " WHEN p.proisagg THEN 'agg'\n"
+ " WHEN 'trigger' =
pg_catalog.format_type(p.prorettype, NULL) THEN 'trigger'\n"
+ " ELSE 'normal'\n"
+ " END AS \"%s\"",
+ gettext_noop("Function
Type"));
+ }
+ else
+ {
+ appendPQExpBuffer(&buf,
+ "\n /* We're in
\"%c\" mode. */\n",
+ function_type);
+ }
+ }
else
+ {
appendPQExpBuffer(&buf,
" CASE WHEN p.proretset THEN 'SETOF
' ELSE '' END ||\n"
" pg_catalog.format_type(p.prorettype, NULL)
as \"%s\",\n"
"
pg_catalog.oidvectortypes(p.proargtypes) as \"%s\"",
gettext_noop("Result data
type"),
gettext_noop("Argument data
types"));
+ if (function_type == 0)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n"
+ " CASE\n"
+ " WHEN p.proisagg THEN 'agg'\n"
+ " WHEN 'trigger' =
pg_catalog.format_type(p.prorettype, NULL) THEN 'trigger'\n"
+ " ELSE 'normal'\n"
+ " END AS \"%s\"",
+ gettext_noop("Function
Type"));
+ }
+ else
+ {
+ appendPQExpBuffer(&buf,
+ "\n /* We're in
\"%c\" mode. */\n",
+ function_type);
+ }
+ }
if (verbose)
appendPQExpBuffer(&buf,
@@ -274,16 +355,36 @@ describeFunctions(const char *pattern, bool verbose, bool
showSystem)
appendPQExpBuffer(&buf,
" LEFT JOIN
pg_catalog.pg_language l ON l.oid = p.prolang\n");
- appendPQExpBuffer(&buf, "WHERE NOT p.proisagg\n");
+ processSQLNamePattern(pset.db, &buf, pattern, false, true,
+ "n.nspname", "p.proname",
NULL,
+
"pg_catalog.pg_function_is_visible(p.oid)");
+
+ switch (function_type)
+ {
+ case 0:
+ break;
+ case 'a':
+ appendPQExpBuffer(&buf," AND p.proisagg\n");
+ break;
+ case 'n':
+ if (pset.sversion >= 80400)
+ appendPQExpBuffer(&buf, " AND NOT
p.proiswindow\n");
+ appendPQExpBuffer(&buf,
+ " AND NOT
p.proisagg\n"
+ " AND
pg_catalog.pg_get_function_result(p.oid) <> 'trigger'\n");
+ break;
+ case 't':
+ appendPQExpBuffer(&buf," AND
pg_catalog.pg_get_function_result(p.oid) = 'trigger'\n");
+ break;
+ case 'w':
+ appendPQExpBuffer(&buf," AND p.proiswindow\n");
+ break;
+ }
if (!showSystem && !pattern)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND
n.nspname <> 'information_schema'\n");
- processSQLNamePattern(pset.db, &buf, pattern, true, false,
- "n.nspname", "p.proname",
NULL,
-
"pg_catalog.pg_function_is_visible(p.oid)");
-
appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
res = PSQLexec(buf.data, false);
@@ -292,7 +393,23 @@ describeFunctions(const char *pattern, bool verbose, bool
showSystem)
return false;
myopt.nullPrint = NULL;
- myopt.title = _("List of functions");
+ switch (function_type)
+ {
+ case 0:
+ myopt.title = _("List of functions");
+ case 'a':
+ myopt.title = _("List of aggregate functions");
+ break;
+ case 'n':
+ myopt.title = _("List of normal functions");
+ break;
+ case 't':
+ myopt.title = _("List of trigger functions");
+ break;
+ case 'w':
+ myopt.title = _("List of windowing functions");
+ break;
+ }
myopt.translate_header = true;
printQuery(res, &myopt, pset.queryFout, pset.logfile);
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 57e5c7b..7f8628a 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -16,7 +16,7 @@ extern bool describeAggregates(const char *pattern, bool
verbose, bool showSyste
extern bool describeTablespaces(const char *pattern, bool verbose);
/* \df */
-extern bool describeFunctions(const char *pattern, bool verbose, bool
showSystem);
+extern bool describeFunctions(const char *pattern, bool verbose, bool
showSystem, const char function_type);
/* \dT */
extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 93ff5d0..2170768 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -174,55 +174,59 @@ slashUsage(unsigned short int pager)
fprintf(output, "\n");
fprintf(output, _("Query Buffer\n"));
- fprintf(output, _(" \\e [FILE] edit the query buffer (or
file) with external editor\n"));
+ fprintf(output, _(" \\e [FILE] edit the query buffer (or
file) with external editor\n"));
fprintf(output, _(" \\ef [FUNCNAME] edit function definition
with external editor\n"));
fprintf(output, _(" \\p show the contents of the
query buffer\n"));
fprintf(output, _(" \\r reset (clear) the query
buffer\n"));
#ifdef USE_READLINE
- fprintf(output, _(" \\s [FILE] display history or save it
to file\n"));
+ fprintf(output, _(" \\s [FILE] display history or save it
to file\n"));
#endif
- fprintf(output, _(" \\w FILE write query buffer to
file\n"));
+ fprintf(output, _(" \\w FILE write query buffer to
file\n"));
fprintf(output, "\n");
fprintf(output, _("Input/Output\n"));
fprintf(output, _(" \\copy ... perform SQL COPY with data
stream to the client host\n"));
- fprintf(output, _(" \\echo [STRING] write string to standard
output\n"));
- fprintf(output, _(" \\i FILE execute commands from
file\n"));
- fprintf(output, _(" \\o [FILE] send all query results to
file or |pipe\n"));
- fprintf(output, _(" \\qecho [STRING] write string to query
output stream (see \\o)\n"));
+ fprintf(output, _(" \\echo [STRING] write string to standard
output\n"));
+ fprintf(output, _(" \\i FILE execute commands from
file\n"));
+ fprintf(output, _(" \\o [FILE] send all query results to
file or |pipe\n"));
+ fprintf(output, _(" \\qecho [STRING] write string to query
output stream (see \\o)\n"));
fprintf(output, "\n");
fprintf(output, _("Informational\n"));
fprintf(output, _(" (options: S = show system objects, + = additional
detail)\n"));
fprintf(output, _(" \\d[S+] list tables, views, and
sequences\n"));
- fprintf(output, _(" \\d[S+] NAME describe table, view,
sequence, or index\n"));
- fprintf(output, _(" \\da[S] [PATTERN] list aggregate
functions\n"));
- fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
- fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n"));
- fprintf(output, _(" \\dC [PATTERN] list casts\n"));
- fprintf(output, _(" \\dd[S] [PATTERN] show comments on
objects\n"));
- fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
- fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n"));
- fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n"));
- fprintf(output, _(" \\dew[+] [PATTERN] list foreign-data
wrappers\n"));
- fprintf(output, _(" \\df[S+] [PATTERN] list functions\n"));
- fprintf(output, _(" \\dF[+] [PATTERN] list text search
configurations\n"));
- fprintf(output, _(" \\dFd[+] [PATTERN] list text search
dictionaries\n"));
- fprintf(output, _(" \\dFp[+] [PATTERN] list text search
parsers\n"));
- fprintf(output, _(" \\dFt[+] [PATTERN] list text search
templates\n"));
- fprintf(output, _(" \\dg [PATTERN] list roles (groups)\n"));
- fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
+ fprintf(output, _(" \\d[S+] NAME describe table, view,
sequence, or index\n"));
+ fprintf(output, _(" \\da[+] [PATTERN] list aggregates\n"));
+ fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
+ fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n"));
+ fprintf(output, _(" \\dC [PATTERN] list casts\n"));
+ fprintf(output, _(" \\dd[S] [PATTERN] show comments on
objects\n"));
+ fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
+ fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n"));
+ fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n"));
+ fprintf(output, _(" \\dew[+] [PATTERN] list foreign-data
wrappers\n"));
+ fprintf(output, _(" \\df[S+] [PATTERN] list functions\n"));
+ fprintf(output, _(" \\dfa[S+] [PATTERN] list aggregate
functions\n"));
+ fprintf(output, _(" \\dfn[S+] [PATTERN] list normal functions\n"));
+ fprintf(output, _(" \\dft[S+] [PATTERN] list trigger
functions\n"));
+ fprintf(output, _(" \\dfw[S+] [PATTERN] list windowing
functions\n"));
+ fprintf(output, _(" \\dF[+] [PATTERN] list text search
configurations\n"));
+ fprintf(output, _(" \\dFd[+] [PATTERN] list text search
dictionaries\n"));
+ fprintf(output, _(" \\dFp[+] [PATTERN] list text search
parsers\n"));
+ fprintf(output, _(" \\dFt[+] [PATTERN] list text search
templates\n"));
+ fprintf(output, _(" \\dg [PATTERN] list roles (groups)\n"));
+ fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
fprintf(output, _(" \\dl list large objects, same
as \\lo_list\n"));
- fprintf(output, _(" \\dn[+] [PATTERN] list schemas\n"));
- fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
- fprintf(output, _(" \\dp [PATTERN] list table, view, and
sequence access privileges\n"));
- fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
- fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
- fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
- fprintf(output, _(" \\du [PATTERN] list roles (users)\n"));
- fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
+ fprintf(output, _(" \\dn[+] [PATTERN] list schemas\n"));
+ fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
+ fprintf(output, _(" \\dp [PATTERN] list table, view, and
sequence access privileges\n"));
+ fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
+ fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
+ fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
+ fprintf(output, _(" \\du [PATTERN] list roles (users)\n"));
+ fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, _(" \\l[+] list all databases\n"));
- fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
+ fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
fprintf(output, "\n");
fprintf(output, _("Formatting\n"));
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
