Skip site navigation (1) Skip section navigation (2)

Re: Overloading functions that are used by operators.

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "Donald Fraser" <demolish(at)cwgsy(dot)net>,"[pgADMIN]" <pgadmin-support(at)postgresql(dot)org>
Subject: Re: Overloading functions that are used by operators.
Date: 2003-07-09 21:58:20
Message-ID: 03AF4E498C591348A42FC93DEA9661B83AF16F@mail.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgadmin-support
 

	-----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.
 
Regards, Dave.

Responses

pgadmin-support by date

Next:From: Donald FraserDate: 2003-07-10 09:31:39
Subject: Re: Overloading functions that are used by operators.
Previous:From: Dave PageDate: 2003-07-09 14:50:29
Subject: Re: pgAdmin website problems(?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group