Lists: | pgsql-hackers |
---|
From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | ANY/SOME/ALL with noncommutable operators |
Date: | 2008-06-19 09:31:02 |
Message-ID: | 200806191131.04643.peter_e@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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?
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: ANY/SOME/ALL with noncommutable operators |
Date: | 2008-06-19 13:11:32 |
Message-ID: | 20080619131132.GB30331@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: ANY/SOME/ALL with noncommutable operators |
Date: | 2008-06-19 14:08:11 |
Message-ID: | 11634.1213884491@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Peter Eisentraut <peter_e(at)gmx(dot)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
From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: ANY/SOME/ALL with noncommutable operators |
Date: | 2008-06-19 14:26:58 |
Message-ID: | 200806191626.58765.peter_e@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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.