ANY/SOME/ALL with noncommutable operators

On Thu, Jun 19, 2008 at 11:31:02AM +0200, Peter Eisentraut wrote:

I can do

'abc' LIKE ANY (ARRAY['a%','b%'])

but not

ANY (ARRAY['abc', 'def']) LIKE '%a'

This seems to be a failing in the SQL standard. You can work around
this by creating your own operators, but maybe there should be a
general solution, as there are a lot of noncommutable operators and
this example doesn't seem all that unuseful in practice.

Comments?

It's been proposed several times before, at least once by Yours Truly,
without objections. I seem to recall it's a SMOP.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Peter Eisentraut <peter_e@gmx.net> writes:

I can do
'abc' LIKE ANY (ARRAY['a%','b%'])

but not

ANY (ARRAY['abc', 'def']) LIKE '%a'

This seems to be a failing in the SQL standard. You can work around this by
creating your own operators, but maybe there should be a general solution, as
there are a lot of noncommutable operators and this example doesn't seem all
that unuseful in practice.

Comments?

Making the commutator operator where you need it *is* a general solution.
I think there's a syntactic-ambiguity reason why the spec is like that...

regards, tom lane

Am Donnerstag, 19. Juni 2008 schrieb Tom Lane:

Making the commutator operator where you need it *is* a general solution.

True. Let me rephrase. The problem is that when dealing with operator names
such as ~~ and &&, coming up with commutator operator names will make a mess
of readability.

I think there's a syntactic-ambiguity reason why the spec is like that...

OK, that might need to be analyzed.