Re: Overloading functions that are used by operators.

----- Original Message -----
From: "Andreas Pflug" <pgadmin(at)pse-consulting(dot)de>
To: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
Cc: "Donald Fraser" <demolish(at)cwgsy(dot)net>; "[pgADMIN]"
<pgadmin-support(at)postgresql(dot)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(at)cwgsy(dot)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.