Thread: Quoting oddities when defining operators in postgres 8.3

This is some oddness about the use of double quotes when defining
operators in postgres 8.3.  

It seems that the operator name in the create operator clause cannot be
quoted, but in the commutator, or negator clauses, if schema-qualified,
the operator must be quoted.  If not schema-qualified it seems there is
no need for quoting.

This works:

create operator public.< ( leftarg = public.seg, rightarg = public.seg, procedure = public.seg_lt, commutator =
public.">",negator = public.">=", restrict = pg_catalog.scalarltsel, join = pg_catalog.scalarltjoinsel
 
);

This does not:

create operator public."<" ( leftarg = public.seg, rightarg = public.seg, procedure = public.seg_lt, commutator =
public.">",negator = public.">=", restrict = pg_catalog.scalarltsel, join = pg_catalog.scalarltjoinsel
 
);

Neither does this:

create operator public.< ( leftarg = public.seg, rightarg = public.seg, procedure = public.seg_lt, commutator =
public.>,negator = public.>=, restrict = pg_catalog.scalarltsel, join = pg_catalog.scalarltjoinsel
 
);

But this does:

create operator public.< ( leftarg = public.seg, rightarg = public.seg, procedure = public.seg_lt, commutator = >,
negator= >=, restrict = pg_catalog.scalarltsel, join = pg_catalog.scalarltjoinsel
 
);

This is no big deal and I have no need of a fix but I thought it odd
enough to be worth reporting.

__
Marc

Marc Munro <marc@bloodnok.com> writes:
> It seems that the operator name in the create operator clause cannot be
> quoted, but in the commutator, or negator clauses, if schema-qualified,
> the operator must be quoted.  If not schema-qualified it seems there is
> no need for quoting.

The correct way to write a schema-qualified operator name isOPERATOR(foo.<)
You can get away without the OPERATOR() decoration immediately after
CREATE OPERATOR, since it's known that an operator name must appear
there, but within the definition-item list the parser is stickier
about this.  It's more or less an implementation artifact that
foo."<" works at all, because that's not an operator name, it's
a regular identifier.
        regards, tom lane