Re: why commutator doesn't work?

po 29. 10. 2018 v 7:35 odesílatel Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
napsal:

>
> 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.
>

yes. I check it in system catalogue, and there are int48pl and int84pl
functions. Thank for info

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

Reason is a reduction of places where the app source code should be
modified. It is migration from Oracle.

Regards

Pavel

>
> --
> Fabien.
>
>