Re: Overloading functions that are used by operators.

Поиск
Список
Период
Сортировка
От Dave Page
Тема Re: Overloading functions that are used by operators.
Дата
Msg-id 03AF4E498C591348A42FC93DEA9661B83AF16F@mail.vale-housing.co.uk
обсуждение исходный текст
Ответ на Overloading functions that are used by operators.  ("Donald Fraser" <demolish@cwgsy.net>)
Ответы Re: Overloading functions that are used by operators.  (Andreas Pflug <pgadmin@pse-consulting.de>)
Список pgadmin-support
Дерево обсуждения
 
-----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.
 
Regards, Dave.

В списке pgadmin-support по дате отправления:

Предыдущее
От: "Dave Page"
Дата:
Сообщение: Re: pgAdmin website problems(?)
Следующее
От: "Donald Fraser"
Дата:
Сообщение: Re: Overloading functions that are used by operators.