Re: Overloading functions that are used by operators.

----- Original Message -----
From: "Andreas Pflug" <pgadmin@pse-consulting.de>
To: "Dave Page" <dpage@vale-housing.co.uk>
Cc: "Donald Fraser" <demolish@cwgsy.net>; "[pgADMIN]"
<pgadmin-support@postgresql.org>
Sent: Thursday, July 10, 2003 11:03 AM
Subject: Re: [pgadmin-support] Overloading functions that are used by
operators.


> Dave Page wrote:
>
> >
> >
> >     -----Original Message-----
> >     *From:* Donald Fraser [mailto:demolish@cwgsy.net]
> >     *Sent:* 09 July 2003 11:04
> >     *To:* [pgADMIN]
> >     *Subject:* Re: [pgadmin-support] Overloading functions that are
> >     used by operators.
> >
> >     Now back to pgAdmin III: now that I have created this new
> >     overloaded function, pgAdmin III will fail when it trys to
> >     populate the "Operators" section of the public schema. Why -
> >     because I now have two functions named 'textcat', which is
> >     perfectly legal but pgAdmin is making an assumption. The
> >     assumption is that the name of the function associated to an
> >     operator defined by pg_operator.oprcode is unique. Operators don't
> >     just use the name of the function to decide which function to call
> >     - they also have all of the information about the arguments. That
> >     is how an operator knows exactly which function to call. Hence
> >     pg_operator.oprcode is not the sole means for deciding which
> >     function will be called, which is what pgAdmin III is assuming.
> >
> >
> > An operator function is selected by it's name, and the left and/or
> > right operands. pgAdmin is doing this because the query uses
> > pg_operator.oprleft and oprright so it knows the types.
> >
> >     I also noticed that in pg_catalog using pgAdmin II there are 643
> >     operators yet pgAdmin III reports only 596?
> >
> >
> > pgAdmin II is correct - there are 643 operators in pg_catalog in
> > PostgreSQL 7.3.x. The major reason why pgAdmin II gets it right is
> > because unlike pgAdmin III it simply does a select on pg_operator. The
> > rest of the details (function/type names etc) are retrieved from
> > internal caches.
> >
> > In pgAdmin III's case, the much more complex query obviously has a
> > problem somewhere as it is not returning the correct number of operators.
> >
> > I think the correct query is:
> >
> > SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner,
> > op.oprkind, op.oprcanhash,
> >                op.oprleft, op.oprright, lt.typname as lefttype,
> > rt.typname as righttype, et.typname as resulttype,
> >                co.oprname as compop, ne.oprname as negop, lso.oprname
> > as leftsortop, rso.oprname as rightsortop,
> >                lco.oprname as lscmpop, gco.oprname as gtcmpop,
> >                po.proname as operproc, pj.proname as joinproc,
> > pr.proname as restrproc, description
> >           FROM pg_operator op
> >           LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
> >           LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
> >           JOIN pg_type et on et.oid=op.oprresult
> >           LEFT OUTER JOIN pg_operator co ON co.oid=op.oprcom
> >           LEFT OUTER JOIN pg_operator ne ON ne.oid=op.oprnegate
> >           LEFT OUTER JOIN pg_operator lso ON lso.oid=op.oprlsortop
> >           LEFT OUTER JOIN pg_operator rso ON rso.oid=op.oprrsortop
> >           LEFT OUTER JOIN pg_operator lco ON lco.oid=op.oprltcmpop
> >           LEFT OUTER JOIN pg_operator gco ON gco.oid=op.oprgtcmpop
> >           JOIN pg_proc po ON po.oid=op.oprcode
> >           LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
> >           LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
> >           LEFT OUTER JOIN pg_description des ON des.objoid=op.oid
> >
> > Note the addition of LEFT OUTER to the lt and rt table joins. It looks
> > like the unary operators were getting ignored.
> >
> > Andreas: Please check this and confirm I'm right.
>
> Ah no, I just thought that binary operators are only half operators, and
> we only want to display complete ones, right? ;-)
>

Well if you try this:
CREATE OPERATOR pg_catalog.!@ (PROCEDURE = 'abs', RIGHTARG = int4);
which is a legal operator and one that you would expect to see, it doesn't show
up with your version of the query but does show up with the above.

Regards
Donald Fraser.