On Fri, Aug 30, 2013 at 5:48 PM, Andres Freund <and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
