Re: psql with "Function Type" in \df - Mailing list pgsql-hackers

David Fetter wrote:
> On Wed, Apr 15, 2009 at 08:54:10PM -0400, Alvaro Herrera wrote:
> > David Fetter wrote:
> >
> > > > > I think it's good to have them translatable.  As for using "aggregate"
> > > > > instead of "agg" I don't think it's that great an idea.  If you need to
> > > > > notify translators that "agg" stands for "aggregate", add a
> > > > > /* translator: */ comment.
> >
> > The "translator: " comment needs to be in the line just above the
> > string.  Right now the PO file looks like this:
> >
> > #: describe.c:243 describe.c:288 describe.c:304
> > msgid "agg"
> > msgstr ""
> >
> > #. translator: "agg" is short for "aggregate"
> > #: describe.c:244
> > msgid "window"
> > msgstr ""
> >
> >
> > Note really what we want ...
>
> Is this any better?

Modified patch applied.  I modified the \? display to be two lines:

  \df[S+] [PATTERN]      list functions
  \df[antwS+] [PATTERN]  list only agg/normal/trigger/window functions

and adjusted the code and documentation a bit.  I removed the
release.sgml patch because that part will be done when the release notes
are updated and I was worried it might confuse things to add it now.

Thanks, the output looks very good.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.223
diff -c -c -r1.223 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    8 Apr 2009 22:29:30 -0000    1.223
--- doc/src/sgml/ref/psql-ref.sgml    21 Apr 2009 15:45:56 -0000
***************
*** 1039,1056 ****

        <varlistentry>
          <term><literal>\df[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>

          <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.
          </para>

          <note>
--- 1039,1060 ----

        <varlistentry>
          <term><literal>\df[S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+         <term><literal>\df[antw][S+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>

          <listitem>
          <para>
!         Lists available functions, together with their arguments,
!         return types, and their function types: 'agg' (aggregate),
!         'normal', 'trigger', and 'window'.  To display only functions
!         of a specific type, use the corresponding letters <literal>a</>,
!         <literal>n</>, <literal>t</>, or <literal>w</>.  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,1070 ****
          </listitem>
        </varlistentry>

-
        <varlistentry>
          <term><literal>\dF[+] [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
          <listitem>
--- 1068,1073 ----
Index: src/bin/psql/command.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.204
diff -c -c -r1.204 command.c
*** src/bin/psql/command.c    25 Mar 2009 13:07:26 -0000    1.204
--- src/bin/psql/command.c    21 Apr 2009 15:45:56 -0000
***************
*** 365,372 ****
              case 'D':
                  success = listDomains(pattern, show_system);
                  break;
!             case 'f':
!                 success = describeFunctions(pattern, show_verbose, show_system);
                  break;
              case 'g':
                  /* no longer distinct from \du */
--- 365,386 ----
              case 'D':
                  success = listDomains(pattern, show_system);
                  break;
!             case 'f':            /* function subsystem */
!                 switch (cmd[2])
!                 {
!                     case '\0':
!                     case '+':
!                     case 'S':
!                     case 'a':
!                     case 'n':
!                     case 't':
!                     case 'w':
!                         success =  describeFunctions(&cmd[2], pattern, show_verbose, show_system);
!                         break;
!                     default:
!                         status = PSQL_CMD_UNKNOWN;
!                         break;
!                 }
                  break;
              case 'g':
                  /* no longer distinct from \du */
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.208
diff -c -c -r1.208 describe.c
*** src/bin/psql/describe.c    8 Apr 2009 22:29:30 -0000    1.208
--- src/bin/psql/describe.c    21 Apr 2009 15:45:56 -0000
***************
*** 183,197 ****


  /* \df
!  * Takes an optional regexp to select particular functions
   */
  bool
! describeFunctions(const char *pattern, bool verbose, bool showSystem)
  {
      PQExpBufferData buf;
      PGresult   *res;
      printQueryOpt myopt = pset.popt;

      initPQExpBuffer(&buf);

      printfPQExpBuffer(&buf,
--- 183,225 ----


  /* \df
!  * Takes an optional regexp to select particular functions.
!  *
!  * As with \d, you can specify the kinds of functions you want:
!  *
!  * a for aggregates
!  * n for normal
!  * t for trigger
!  * w for window
!  *
!  * and you can mix and match these in any order.
   */
  bool
! describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem)
  {
+     bool            showAggregate = strchr(functypes, 'a') != NULL;
+     bool            showNormal = strchr(functypes, 'n') != NULL;
+     bool            showTrigger = strchr(functypes, 't') != NULL;
+     bool            showWindow = strchr(functypes, 'w') != NULL;
+
      PQExpBufferData buf;
      PGresult   *res;
      printQueryOpt myopt = pset.popt;

+     if (showWindow && 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;
+     }
+
+     if (!showAggregate && !showNormal && !showTrigger && !showWindow)
+     {
+         showAggregate = showNormal = showTrigger = true;
+         if (pset.sversion >= 80400)
+             showWindow = true;
+     }
+
      initPQExpBuffer(&buf);

      printfPQExpBuffer(&buf,
***************
*** 203,211 ****
      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"));
      else if (pset.sversion >= 80100)
          appendPQExpBuffer(&buf,
                        "  CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
--- 231,251 ----
      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\",\n"
!                           " CASE\n"
!                           "  WHEN p.proisagg THEN '%s'\n"
!                           "  WHEN p.proiswindow THEN '%s'\n"
!                           "  WHEN pg_catalog.pg_get_function_result(p.oid) = 'trigger' THEN '%s'\n"
!                           "  ELSE '%s'\n"
!                           "END as \"%s\"",
                            gettext_noop("Result data type"),
!                               gettext_noop("Argument data types"),
!                               /* translator: "agg" is short for "aggregate" */
!                               gettext_noop("agg"),
!                               gettext_noop("window"),
!                               gettext_noop("trigger"),
!                               gettext_noop("normal"),
!                               gettext_noop("Type"));
      else if (pset.sversion >= 80100)
          appendPQExpBuffer(&buf,
                        "  CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
***************
*** 238,253 ****
                        "      FROM\n"
                        "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
                        "    ), ', ')\n"
                        "  END AS \"%s\"",
                            gettext_noop("Result data type"),
!                           gettext_noop("Argument data types"));
      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 (verbose)
          appendPQExpBuffer(&buf,
--- 278,313 ----
                        "      FROM\n"
                        "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
                        "    ), ', ')\n"
+                       "  END AS \"%s\",\n"
+                       "  CASE\n"
+                       "    WHEN p.proisagg THEN '%s'\n"
+                       "    WHEN 'trigger' = pg_catalog.format_type(p.prorettype, NULL) THEN '%s'\n"
+                       "    ELSE '%s'\n"
                        "  END AS \"%s\"",
                            gettext_noop("Result data type"),
!                           gettext_noop("Argument data types"),
!                           /* translator: "agg" is short for "aggregate" */
!                           gettext_noop("agg"),
!                           gettext_noop("trigger"),
!                           gettext_noop("normal"),
!                           gettext_noop("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\",\n"
!                       "  CASE\n"
!                       "    WHEN p.proisagg THEN '%s'\n"
!                       "    WHEN 'trigger' = pg_catalog.format_type(p.prorettype, NULL) THEN '%s'\n"
!                       "    ELSE '%s'\n"
!                       "  END AS \"%s\"",
                            gettext_noop("Result data type"),
!                           gettext_noop("Argument data types"),
!                           /* translator: "agg" is short for "aggregate" */
!                           gettext_noop("agg"),
!                           gettext_noop("trigger"),
!                           gettext_noop("normal"),
!                           gettext_noop("Type"));

      if (verbose)
          appendPQExpBuffer(&buf,
***************
*** 274,289 ****
          appendPQExpBuffer(&buf,
                            "     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");

!     appendPQExpBuffer(&buf, "WHERE NOT p.proisagg\n");

       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);
--- 334,396 ----
          appendPQExpBuffer(&buf,
                            "     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");

!     processSQLNamePattern(pset.db, &buf, pattern, false, true,
!                           "n.nspname", "p.proname", NULL,
!                           "pg_catalog.pg_function_is_visible(p.oid)");
!
!     if (showNormal && showAggregate && showTrigger && showWindow)
!         /* Do nothing */;
!     else if (showNormal)
!     {
!         if (!showWindow && pset.sversion >= 80400)
!                 appendPQExpBuffer(&buf, "      AND NOT p.proiswindow\n");
!         if (!showAggregate)
!             appendPQExpBuffer(&buf, "      AND NOT p.proisagg\n");
!         if (!showTrigger)
!         {
!             if (pset.sversion >= 80400)
!                 appendPQExpBuffer(&buf,
!                                   "      AND pg_catalog.pg_get_function_result(p.oid) <> 'trigger'\n");
!             else
!                 appendPQExpBuffer(&buf,
!                                   "      AND pg_catalog.format_type(p.prorettype, NULL) <> 'trigger'\n");
!         }
!     }
!     else
!     {
!         bool    needs_or = false;
!
!         appendPQExpBuffer(&buf, "      AND (\n         ");
!         if (showAggregate)
!         {
!             appendPQExpBuffer(&buf,"p.proisagg\n");
!             needs_or = true;
!         }
!         if (showTrigger)
!         {
!             if (needs_or)
!                 appendPQExpBuffer(&buf, "      OR ");
!             if (pset.sversion >= 80400)
!                 appendPQExpBuffer(&buf,
!                                   "pg_catalog.pg_get_function_result(p.oid) = 'trigger'\n");
!             else
!                 appendPQExpBuffer(&buf,
!                                   "'trigger' <> pg_catalog.format_type(p.prorettype, NULL)\n");
!             needs_or = true;
!         }
!         if (showWindow)
!         {
!             if (needs_or)
!                 appendPQExpBuffer(&buf, "      OR ");
!             appendPQExpBuffer(&buf, "p.proiswindow\n");
!         }
!         appendPQExpBuffer(&buf, "      )\n");
!     }

       if (!showSystem && !pattern)
           appendPQExpBuffer(&buf, "      AND n.nspname <> 'pg_catalog'\n"
                                   "      AND n.nspname <> 'information_schema'\n");

      appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");

      res = PSQLexec(buf.data, false);
Index: src/bin/psql/describe.h
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.h,v
retrieving revision 1.39
diff -c -c -r1.39 describe.h
*** src/bin/psql/describe.h    20 Jan 2009 02:13:42 -0000    1.39
--- src/bin/psql/describe.h    21 Apr 2009 15:45:56 -0000
***************
*** 15,22 ****
  /* \db */
  extern bool describeTablespaces(const char *pattern, bool verbose);

! /* \df */
! extern bool describeFunctions(const char *pattern, bool verbose, bool showSystem);

  /* \dT */
  extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
--- 15,22 ----
  /* \db */
  extern bool describeTablespaces(const char *pattern, bool verbose);

! /* \df, \dfa, \dfn, \dft, \dfw, etc. */
! extern bool describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem);

  /* \dT */
  extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
Index: src/bin/psql/help.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.146
diff -c -c -r1.146 help.c
*** src/bin/psql/help.c    11 Apr 2009 14:11:21 -0000    1.146
--- src/bin/psql/help.c    21 Apr 2009 15:45:56 -0000
***************
*** 196,202 ****
      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"));
--- 196,202 ----
      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[+]  [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"));
***************
*** 206,211 ****
--- 206,212 ----
      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[antwS+] [PATTERN]  list only agg/normal/trigger/window 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"));
Index: src/test/regress/expected/polymorphism.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/polymorphism.out,v
retrieving revision 1.19
diff -c -c -r1.19 polymorphism.out
*** src/test/regress/expected/polymorphism.out    9 Feb 2009 21:18:28 -0000    1.19
--- src/test/regress/expected/polymorphism.out    21 Apr 2009 15:45:57 -0000
***************
*** 838,846 ****
  -- verify it lists properly
  \df dfunc
                                         List of functions
!  Schema | Name  | Result data type |                    Argument data types
! --------+-------+------------------+-----------------------------------------------------------
!  public | dfunc | integer          | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2
  (1 row)

  drop function dfunc(int, int);
--- 838,846 ----
  -- verify it lists properly
  \df dfunc
                                         List of functions
!  Schema | Name  | Result data type |                    Argument data types                    |  Type
! --------+-------+------------------+-----------------------------------------------------------+--------
!  public | dfunc | integer          | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | normal
  (1 row)

  drop function dfunc(int, int);
***************
*** 1006,1014 ****
  HINT:  Use DROP FUNCTION first.
  \df dfunc
                                    List of functions
!  Schema | Name  | Result data type |               Argument data types
! --------+-------+------------------+-------------------------------------------------
!  public | dfunc | integer          | VARIADIC a integer[] DEFAULT ARRAY[]::integer[]
  (1 row)

  drop function dfunc(a variadic int[]);
--- 1006,1014 ----
  HINT:  Use DROP FUNCTION first.
  \df dfunc
                                    List of functions
!  Schema | Name  | Result data type |               Argument data types               |  Type
! --------+-------+------------------+-------------------------------------------------+--------
!  public | dfunc | integer          | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | normal
  (1 row)

  drop function dfunc(a variadic int[]);