Re: Bug: 8.0 beta1 either view optimization or pgdump/pgrestore

As I was not using any of those duplicate columns, * was easier to use
and I did not think about trying to use the other ones.
In fact when you do try to use one of those columns in the query, it
doesn't allow the query because of ambiguous columns.

Thank you for fixing this particular problem, even though it doesn't
solve the global question it does allow you to seamlessly backup and
restore functions that work correctly in PG. I do not think that
anything needs to be answered in this regards, because if you wanted
to actually use any of those fields it would give you the same
ambiguity error and you would have to change the query to use aliases
for the identical field names. In any case, a query trying to use identical
field names would not work to begin with, so there is no call to fix
anything so long as it does not change existing behavior.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

Sim Zacks <sim(at)compulab(dot)co(dot)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