Thread: why commutator doesn't work?

Hello Pavel,

> I try to create operator + for varchar and integer with Oracle behave.
>
> create or replace function sum(varchar, int)
> returns int as $$ select $1::int + $2 $$ language sql;
>
> create operator + (function = sum, leftarg = varchar, rightarg = int, commutator = +);
>
> create table foo2(a varchar);
> insert into foo2 values('10');
> select a + 1 from foo2; -- it is ok
>
> but
>
> select 1 + a from foo2; -- fails
>
> ERROR:  operator is only a shell: integer + character varying
> LINE 1: select 1 + a  from foo2;
>
> Why? This should be solved by COMMUTATOR = +

Nope. I understand commutator to be an optimization thing declaration for 
joins, it does not create another operator per se.

See https://www.postgresql.org/docs/current/static/xoper-optimization.html#id-1.8.3.17.4

Se in particular the end paragraph: you have created a "dummy" because 
there is no existing int+varchar operator.

Probably you want to declare another (varchar, int) function and another 
operator for varchar + int, which has the initial one as a commutator.

I'm wondering whether you could use varchar to int implicit cast instead.

-- 
Fabien.