Re: Bug: 8.0 beta1 either view optimization or pgdump/pgrestore - Mailing list pgsql-general

Sim Zacks <sim@compulab.co.il> writes:
> /*Here is the virtual table I mentioned using select * on a join*/
> (select * from PackagePricingGroups b Inner JOIN PricingGroups c ON b.PricingGroupID = c.PricingGroupID) groups

Okay, evidently the problem is that you have identically named
columns in the two tables PackagePricingGroups and PricingGroups,
so the "groups" join contains duplicate column names.  (AFAICS this
is not illegal per the SQL spec, but I wonder whether it shouldn't
be, because it's very hard to avoid ambiguity.)

I've tweaked ruleutils.c for 8.0 so that the display looks like

...
  LEFT JOIN ( SELECT b.packagepricinggroupid, b.pricinggroupid,
              b.packageid, b.createuserid, b.createdate, b.modifyuserid,
              b.modifydate, c.pricinggroupid, c.description,
              c.supplierid, c.baseprice, c.priceperpin, c.currencyid,
              c.createuserid, c.createdate, c.modifyuserid, c.modifydate
              FROM packagepricinggroups b
                   JOIN pricinggroups c ON
...

which solves this particular issue.  I'm not sure a complete solution is
possible in the presence of duplicate column names; perhaps you should
modify the query to avoid that.

            regards, tom lane