Thread: pg_get_viewdef returns one paren too much

pg_get_viewdef returns one paren too much

From
Andreas Pflug
Date:
laurie.burrow@powerconv.alstom.com noticed a problem with pg_get_viewdef
in prettyprint mode.

create table gnrcitm (gnrcitmid int);
create table gnrcitmothrref (idntfyrefid int, gnrcitmid int);
create table other_ref(idntfyrefid int, catnmeclssid text, actvle text);

CREATE OR REPLACE VIEW test_view AS
  SELECT or0.actvle AS treename
    FROM gnrcitm g
    LEFT JOIN (gnrcitmothrref g0
    JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
r0.catnmeclssid::text = 'Tree Name'::text) or0
  ON g.gnrcitmid = or0.gnrcitmid;

pg_get_viewdef(viewoid, true) will return

CREATE OR REPLACE VIEW test_view AS
  SELECT or0.actvle AS treename
    FROM gnrcitm g
    LEFT JOIN
     ( -- <<<
              (gnrcitmothrref g0
    JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
r0.catnmeclssid = 'Tree Name'::text) or0
     ) -- <<<
  ON g.gnrcitmid = or0.gnrcitmid;


The attached patch corrects this, without affecting the following:

CREATE OR REPLACE VIEW test_view2 AS
  SELECT r0.actvle AS treename
    FROM gnrcitm g
    LEFT JOIN (gnrcitmothrref g0
    JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
r0.catnmeclssid::text = 'Tree Name'::text)
  ON g.gnrcitmid = g0.gnrcitmid


Regards,
Andreas


Index: ruleutils.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.185
diff -u -r1.185 ruleutils.c
--- ruleutils.c    5 Nov 2004 19:16:11 -0000    1.185
+++ ruleutils.c    10 Dec 2004 13:41:17 -0000
@@ -3876,7 +3876,8 @@
         bool        need_paren_on_right;

         need_paren_on_right = PRETTY_PAREN(context) &&
-            !IsA(j->rarg, RangeTblRef);
+          !IsA(j->rarg, RangeTblRef) &&
+          !(IsA(j->rarg, JoinExpr) && ((JoinExpr*)j->rarg)->alias != NULL);

         if (!PRETTY_PAREN(context) || j->alias != NULL)
             appendStringInfoChar(buf, '(');

Re: pg_get_viewdef returns one paren too much

From
Tom Lane
Date:
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> laurie.burrow@powerconv.alstom.com noticed a problem with pg_get_viewdef
> in prettyprint mode.

Applied in HEAD and 7.4 branches.

            regards, tom lane