Re: operator precedence issues - Mailing list pgsql-hackers

On Fri, Aug 30, 2013 at 5:48 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> Hi,
>
> On 2013-08-30 17:35:04 -0500, Merlin Moncure wrote:
>> "When a schema-qualified operator name is used in the OPERATOR syntax,
>> as for example in:
>> SELECT 3 OPERATOR(pg_catalog.+) 4;
>> the OPERATOR construct is taken to have the default precedence shown
>> in Table 4-2 for "any other" operator. This is true no matter which
>> specific operator appears inside OPERATOR()."
>>
>> That rule seems intentionally designed to make it impossible to to
>> override mathematical behaviors.  Mainly curious -- was that
>> intentional?
>
> You can change your search_path to include your schema before an
> explicitly listed pg_catalog afair. Not nice, but should work...

hurk -- wish I had known that last week, but that's a nifty trick!  It
satisfies my particular problem (safe division) since in this case the
problem is handled 'in function' and I can temporarily hack the
search_path.  Interestingly, if you do this the database doesn't match

Ideally though you could specify operator precedence in the operator
name itself though in such a way that bison pick it up.  I don't know
if that's possible since so many operator names have been given out
without any thought to reserving characters for precedence, or if it
would be worth the extra parsing time even if you could do it.
Overriding stock operator behaviors is a really dodgy practice with
the limited but important exception of handling certain classes of
mathematical errors.

While playing around with Andres's trick, I noticed that it works but
will not match against operators taking "any" although those will
match with explicit schema declaration (FWICT it goes through the
search_path trying to explicitly match int/int operator then goes
again matches "any").  That's pretty weird:

postgres=# CREATE OR REPLACE FUNCTION SafeDiv(
postgres(#   anyelement,
postgres(#   anyelement) RETURNS anyelement AS
postgres-# $$
postgres$#   SELECT $1 OPERATOR(pg_catalog./)  NULLIF($2, 0);
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION

postgres=# set search_path to safediv, pg_catalog, public;
SET

postgres=# CREATE OPERATOR safediv./
postgres-# (
postgres(#   PROCEDURE = SafeDiv,
postgres(#   LEFTARG = anyelement,
postgres(#   RIGHTARG = anyelement,
postgres(#   COMMUTATOR = /
postgres(# );
CREATE OPERATOR

postgres=# select 1/0;
ERROR:  division by zero
postgres=# select 1 operator(safediv./) 0;?column?
----------

(1 row)

postgres=# CREATE OR REPLACE FUNCTION SafeDiv(
postgres(#   int4,
postgres(#   int4) RETURNS int4 AS
postgres-# $$
postgres$#   SELECT $1 OPERATOR(pg_catalog./) NULLIF($2, 0);
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION

postgres=#
postgres=# CREATE OPERATOR safediv./
postgres-# (
postgres(#   PROCEDURE = SafeDiv,
postgres(#   LEFTARG = int4,
postgres(#   RIGHTARG = int4,
postgres(#   COMMUTATOR = /
postgres(# );
CREATE OPERATOR

postgres=# select 1/0;?column?
----------

(1 row)





merlin