[EMAIL PROTECTED] 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, '(');
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to