Re: pretty print viewdefs - Mailing list pgsql-hackers

Tom Lane wrote:
> Greg Stark <gsstark@mit.edu> writes:
>
>> Incidentally I just tried
>> \d information_schema.views
>>
>
>
>> and it *does* seem to put newlines after some of the target list
>> items. After each of the CASE expressions it puts a newline. So you
>> *already* get a mixture of some multiple items on a line and some
>> one-per-line.
>>
>
> Yeah, sufficiently complex expressions (sub-selects, for an obvious
> case) will get internal pretty-printing that might include newlines.
>


OK, drawing this together, what I did was to go back closer to my
original idea, but put this in a separate function, so nobody would get
too upset ;-)

Here is what my function does, and also what the current "pretty
printing" does:

    andrew=# select pg_get_viewdef_long('foo');
         pg_get_viewdef_long
    ------------------------------
      SELECT 'a'::text AS b,
             ( SELECT 1
                FROM dual) AS x,
             random() AS y,
             CASE
                 WHEN true THEN 1
                 ELSE 0
             END AS c,
             1 AS d
        FROM dual;
    (1 row)

    andrew=# select pg_get_viewdef('foo',true);
                   pg_get_viewdef
    ---------------------------------------------
      SELECT 'a'::text AS b, ( SELECT 1
                FROM dual) AS x, random() AS y,
             CASE
                 WHEN true THEN 1
                 ELSE 0
             END AS c, 1 AS d
        FROM dual;
    (1 row)


WIP Patch is attached. To complete it I would add a psql option to use
it, but maybe we should have a psql setting to enable it ... building
something extra into the \d* stuff looks a bit ugly, since we already
have a million options.

cheers

andrew


Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.549
diff -c -r1.549 pg_proc.h
*** src/include/catalog/pg_proc.h    4 Aug 2009 04:04:12 -0000    1.549
--- src/include/catalog/pg_proc.h    27 Aug 2009 14:22:04 -0000
***************
*** 4071,4076 ****
--- 4071,4080 ----
  DESCR("select statement of a view with pretty-print option");
  DATA(insert OID = 2506 (  pg_get_viewdef       PGNSP PGUID 12 1 0 0 f f f t f s 2 0 25 "26 16" _null_ _null_ _null_
_null_   pg_get_viewdef_ext _null_ _null_ _null_ )); 
  DESCR("select statement of a view with pretty-print option");
+ DATA(insert OID = 2336 (  pg_get_viewdef_long       PGNSP PGUID 12 1 0 0 f f f t f s 1 0 25 "25" _null_ _null_ _null_
_null_   pg_get_viewdef_name_long _null_ _null_ _null_ )); 
+ DESCR("select statement of a view with pretty-printing, columns line separated");
+ DATA(insert OID = 2337 (  pg_get_viewdef_long       PGNSP PGUID 12 1 0 0 f f f t f s 1 0 25 "26" _null_ _null_ _null_
_null_   pg_get_viewdef_long _null_ _null_ _null_ )); 
+ DESCR("select statement of a view with pretty-printing, columns line separated");
  DATA(insert OID = 2507 (  pg_get_indexdef       PGNSP PGUID 12 1 0 0 f f f t f s 3 0 25 "26 23 16" _null_ _null_
_null__null_    pg_get_indexdef_ext _null_ _null_ _null_ )); 
  DESCR("index description (full create statement or single expression) with pretty-print option");
  DATA(insert OID = 2508 (  pg_get_constraintdef PGNSP PGUID 12 1 0 0 f f f t f s 2 0 25 "26 16" _null_ _null_ _null_
_null_   pg_get_constraintdef_ext _null_ _null_ _null_ )); 
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v
retrieving revision 1.338
diff -c -r1.338 builtins.h
*** src/include/utils/builtins.h    4 Aug 2009 16:08:36 -0000    1.338
--- src/include/utils/builtins.h    27 Aug 2009 14:22:05 -0000
***************
*** 582,589 ****
--- 582,591 ----
  extern Datum pg_get_ruledef_ext(PG_FUNCTION_ARGS);
  extern Datum pg_get_viewdef(PG_FUNCTION_ARGS);
  extern Datum pg_get_viewdef_ext(PG_FUNCTION_ARGS);
+ extern Datum pg_get_viewdef_long(PG_FUNCTION_ARGS);
  extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);
  extern Datum pg_get_viewdef_name_ext(PG_FUNCTION_ARGS);
+ extern Datum pg_get_viewdef_name_long(PG_FUNCTION_ARGS);
  extern Datum pg_get_indexdef(PG_FUNCTION_ARGS);
  extern Datum pg_get_indexdef_ext(PG_FUNCTION_ARGS);
  extern char *pg_get_indexdef_string(Oid indexrelid);
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c    1 Aug 2009 19:59:41 -0000    1.306
--- src/backend/utils/adt/ruleutils.c    27 Aug 2009 14:22:05 -0000
***************
*** 71,80 ****
--- 71,83 ----
  /* Pretty flags */
  #define PRETTYFLAG_PAREN        1
  #define PRETTYFLAG_INDENT        2
+ #define PRETTYFLAG_ONETARGET    4

  /* macro to test if pretty action needed */
  #define PRETTY_PAREN(context)    ((context)->prettyFlags & PRETTYFLAG_PAREN)
  #define PRETTY_INDENT(context)    ((context)->prettyFlags & PRETTYFLAG_INDENT)
+ #define PRETTY_ONETARGET(context)    \
+     ((context)->prettyFlags & PRETTYFLAG_ONETARGET)


  /* ----------
***************
*** 350,355 ****
--- 353,369 ----
  }

  Datum
+ pg_get_viewdef_long(PG_FUNCTION_ARGS)
+ {
+     /* By OID */
+     Oid            viewoid = PG_GETARG_OID(0);
+     int            prettyFlags;
+
+     prettyFlags = PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_ONETARGET;
+     PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags)));
+ }
+
+ Datum
  pg_get_viewdef_name(PG_FUNCTION_ARGS)
  {
      /* By qualified name */
***************
*** 381,386 ****
--- 395,416 ----
      PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags)));
  }

+ Datum
+ pg_get_viewdef_name_long(PG_FUNCTION_ARGS)
+ {
+     /* By qualified name */
+     text       *viewname = PG_GETARG_TEXT_P(0);
+     int            prettyFlags;
+     RangeVar   *viewrel;
+     Oid            viewoid;
+
+     prettyFlags = PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_ONETARGET;
+     viewrel = makeRangeVarFromNameList(textToQualifiedNameList(viewname));
+     viewoid = RangeVarGetRelid(viewrel, false);
+
+     PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags)));
+ }
+
  /*
   * Common code for by-OID and by-name variants of pg_get_viewdef
   */
***************
*** 2651,2656 ****
--- 2681,2687 ----
      char       *sep;
      int            colno;
      ListCell   *l;
+     int         save_len;

      sep = " ";
      colno = 0;
***************
*** 2666,2671 ****
--- 2697,2703 ----
          appendStringInfoString(buf, sep);
          sep = ", ";
          colno++;
+         save_len = buf->len;

          /*
           * We special-case Var nodes rather than using get_rule_expr. This is
***************
*** 2703,2708 ****
--- 2735,2764 ----
              if (attname == NULL || strcmp(attname, colname) != 0)
                  appendStringInfo(buf, " AS %s", quote_identifier(colname));
          }
+
+         /* insert a line break if we don't already have one */
+         if ((PRETTY_ONETARGET(context)) && colno > 1 && buf->data[save_len] != '\n')
+         {
+             StringInfoData thiscol;
+
+             initStringInfo(&thiscol);
+
+             /* save what we just added */
+             appendStringInfoString(&thiscol,buf->data + save_len);
+
+             /* wipe it out from the buffer */
+             buf->len = save_len;
+             buf->data[save_len] = '\0';
+
+             /* add a line break and reindent */
+             appendContextKeyword(context, "", -PRETTYINDENT_STD,
+                                  PRETTYINDENT_STD, PRETTYINDENT_STD);
+
+             /* and now put back what we wiped out */
+             appendStringInfoString(buf,thiscol.data);
+
+             pfree(thiscol.data);
+         }
      }
  }