Thread: 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 ASSELECT or0.actvle AS treename FROM gnrcitm g LEFT JOIN (gnrcitmothrref g0 JOINother_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 ASSELECT or0.actvle AS treename FROM gnrcitm g LEFT JOIN ((gnrcitmothrref g0 JOIN other_refr0 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.
Hi Andreas (again!) I haven't even looked at this one - I assume it's likely to be your reformatting code that I never quite got my head around? Also, to save writing another email - I notice when connecting now a black rectangle is shown briefly. I assume this is the status message that you added? If so, it never seems to draw properly on Windows. Dunno about Unix. Regards, Dave. > -----Original Message----- > From: pgadmin-support-owner@postgresql.org > [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of > laurie.burrow@powerconv.alstom.com > Sent: 06 December 2004 13:25 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] 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. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend >
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 10 December 2004 10:44 > To: Dave Page > Cc: laurie.burrow@powerconv.alstom.com; pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Reverse engineering problem on views > > > Displays correctly on both win32 and unix for me. Is your > machine too fast? Try connecting to a non-running server. No > problem if it just flashes on fast connections. On a non-running server it is black for 5 seconds or so until the error message box displays - then it draws. My machine is a 1.7GHz Pentium M laptop, with 1GB of RAM, so it's not the fastest, but far from unusable :-) /D
Dave Page wrote: > Hi Andreas (again!) > > I haven't even looked at this one - I assume it's likely to be your > reformatting code that I never quite got my head around? Still waiting for review in my mailbox. > Also, to save writing another email - I notice when connecting now a > black rectangle is shown briefly. I assume this is the status message > that you added? If so, it never seems to draw properly on Windows. > Dunno about Unix. Displays correctly on both win32 and unix for me. Is your machine too fast? Try connecting to a non-running server. No problem if it just flashes on fast connections. Regards, Andreas
laurie.burrow@powerconv.alstom.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