| From: | "Donald Fraser" <demolish(at)cwgsy(dot)net> | 
|---|---|
| To: | "[ADMIN]" <pgsql-admin(at)postgresql(dot)org> | 
| Cc: | "[pgADMIN]" <pgadmin-support(at)postgresql(dot)org> | 
| Subject: | Overloading functions that are used by operators. | 
| Date: | 2003-07-08 20:00:09 | 
| Message-ID: | 00c801c3458b$8c139490$1664a8c0@DEMOLITION | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgadmin-support pgsql-admin | 
I have a question regarding operators which are causing problems with the new pgAdmin III software being developed and my database.
The problem has been discussed in depth with the pgAdmin III development team and they have not been able to resolve the issue and suggested that I have either done something that I shouldn't have of or that there is a possible bug with postgresql.
Background information:
I have my own data type ('citext') that uses many of the built-in system functions based on the fact that the storage is identical to the data type 'text'.
For example I have overloaded the function 'textcat' and the definition I have used is:
CREATE FUNCTION pg_catalog.textcat(citext, citext) RETURNS citext AS 'textcat' LANGUAGE 'internal' IMMUTABLE STRICT;
When you view the functions via the pg_proc table you will see that there are two functions with 'textcat' in the column pg_proc.proname.
I have been using the database for over a year and had no reported problems with using the operator || on data types 'text', 'varchar', 'bpchar' or calling the overloaded function 'textcat(citext, citext)'.
First question:
Am I allowed to overload functions, for example the 'textcat' function, when they are being used by system operators?
If the answer is no then read no further - the problem is mine and I will have to work around it.
The system has at least three operators such as:
||(text,text)
||(varchar, text)
||(bpchar, text)
All use the function 'textcat'.
Now the new pgAdmin III software interrogates the pg_operator table to extract as much information as possible about the operators.
They are using the following SQL statement to retrieve the information that they want.
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 
JOIN pg_type lt ON lt.oid=op.oprleft 
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 
WHERE op.oprnamespace = 2200::oid 
ORDER BY op.oprname
The offending part of this SQL statement is the join:
JOIN pg_proc po ON po.oid=op.oprcode
It produces the error message "ERROR: There is more than one procedure named textcat".
Investigation into this has shown that the type conversion of the column named pg_operator.oprcode to type oid is done by the function call to:
regprocin('textcat')
My question would be - what is the correct way to find the function's oid that this operator uses? But first read on in case it is not necessary...
It seems to me that the join statements they are using are unnecessary so long as the column pg_operator.oprcode has the identical name to its matching function that has its name defined by column pg_proc.proname, which seems to be the only data that they are using.
On a similar basis, do the columns named pg_operator.oprjoin and pg_operator.oprrest have identical names defined by the column pg_proc.proname for which they are also retrieving? If so then those corresponding joins are also unnecessarily.
Many thanks in advance,
regards
Donald Fraser.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dave Page | 2003-07-08 20:12:04 | Re: Overloading functions that are used by operators. | 
| Previous Message | Dave Page | 2003-07-08 15:55:33 | Re: Problem using Database Migration Wizard | 
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dave Page | 2003-07-08 20:12:04 | Re: Overloading functions that are used by operators. | 
| Previous Message | Andrew Sullivan | 2003-07-08 05:02:00 | Re: PostgreSQL settings for 12GB RAM |