Reverse engineering problem on views

Hi,

I have noticed this possible bug:

Using pgadmin V1.2.0 on Windows 2000 Desktop to a remote Windows 2000
Server Postgres V7.4.1 running on cygwin and to Postgres V8Beta2

Created this view.

CREATE OR REPLACE VIEW public.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

Retrieved view into SQL dialog window using copy SQL option

This gave

-- View: "test_view"

-- DROP VIEW test_view;

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;

When executed this gives ERROR: syntax error at or near ")" at character
273.

There seems to be an error caused by the reverse engineering adding the
extra bracketting on the left join clauses. If the extra brackets are
deleted the SQLis executed correctly.

Any suggestions?

Great work pgadmin, really enjoy using it.

Regards
Laurie Burrow

:.________________
CONFIDENTIALITY : This e-mail and any attachments are confidential and
may be privileged. If you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.

laurie(dot)burrow(at)powerconv(dot)alstom(dot)com wrote:

> Retrieved view into SQL dialog window using copy SQL option
>
> This gave
>
> -- View: "test_view"
>
> -- DROP VIEW test_view;
>
> 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;
>
> When executed this gives ERROR: syntax error at or near ")" at character
> 273.

I can confirm this; the backend's pg_get_viewdef will give these
parentheses too much with pretty-print option=true. Quite awkward, since
this option should *suppress* additional parens...

I'll check the backend code.

Regards,
Andreas

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

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> laurie(dot)burrow(at)powerconv(dot)alstom(dot)com noticed a problem with pg_get_viewdef
> in prettyprint mode.

Applied in HEAD and 7.4 branches.

regards, tom lane