Skip site navigation (1) Skip section navigation (2)

pg_get_viewdef returns one paren too much

From: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: laurie(dot)burrow(at)powerconv(dot)alstom(dot)com,PostgreSQL Patches <pgsql-patches(at)postgresql(dot)org>
Subject: pg_get_viewdef returns one paren too much
Date: 2004-12-10 13:55:01
Message-ID: 41B9AAB5.4030900@pse-consulting.de (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-patches
laurie(dot)burrow(at)powerconv(dot)alstom(dot)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



Attachment: ruleutils.diff
Description: text/x-patch (634 bytes)

In response to

Responses

pgadmin-support by date

Next:From: Raphaƫl EnriciDate: 2004-12-11 09:38:39
Subject: Re: [pgadmin-support] crash
Previous:From: Andreas PflugDate: 2004-12-10 11:34:15
Subject: Re: Reverse engineering problem on views

pgsql-patches by date

Next:From: Andrew DunstanDate: 2004-12-11 00:57:40
Subject: Re: regression script/makefile exit failure
Previous:From: Andreas PflugDate: 2004-12-10 11:34:15
Subject: Re: Reverse engineering problem on views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group