From: Donald Fraser [mailto:demolish(at)cwgsy(dot)net]
Sent: 09 July 2003 11:04
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
In pgAdmin III's case, the much more complex query obviously has a
problem somewhere as it is not returning the correct number of
I think the correct query is:
SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as opowner,
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.
pgadmin-support by date
|Next:||From: Donald Fraser||Date: 2003-07-10 09:31:39|
|Subject: Re: Overloading functions that are used by operators.|
|Previous:||From: Dave Page||Date: 2003-07-09 14:50:29|
|Subject: Re: pgAdmin website problems(?)|