Re: View prevents index

On Thu, Jul 12, 2001 at 04:25:47PM +1000, Martijn van Oosterhout wrote:
> On Wed, Jul 11, 2001 at 11:00:18PM -0700, Eric G. Miller wrote:
> > 2. (SELECT foo.id As "Id", foo.blurb As "Stuff"
> > WHERE character_length ("Stuff") > 80)
> > UNION
> > (SELECT bar.bar_id As "Id", bar.data As "Stuff"
> > WHERE character_length("Stuff") > 80);
> >
> > I guess the second would fail on the alias, but the first should
> > succeed ??
>
> What's wrong with that query. Looks fine to me.

Well. pgsql doesn't handle column aliases in where clauses now...

But in the first example, I would expect the relation formed by the
inner union to have columns known as "Id" and "Stuff" so the outer WHERE
would have to use those names. What would you do otherwise? Use the
names from the first select? Clearly that won't transfer well to the
WHERE clause in the second scenario without some name substitution.

1. (SELECT id FROM foo UNION SELECT key FROM bar) WHERE <id|key> = 2 ??

2. (SELECT id FROM foo WHERE id = 2)
UNION
(SELECT key FROM bar WHERE key = 2)

And, I guess you're right about aggregates as well. I still don't see
the constructs as being equivalent due to the name changes that occur
in a UNION.

--
Eric G. Miller <egm2(at)jps(dot)net>