Re: Overloading functions that are used by operators.

From: Andreas Pflug <pgadmin(at)pse-consulting(dot)de>
To: Donald Fraser <demolish(at)cwgsy(dot)net>
Cc: "[pgADMIN]" <pgadmin-support(at)postgresql(dot)org>
Subject: Re: Overloading functions that are used by operators.
Date: 2003-07-08 21:05:20
Message-ID: 3F0B3210.1080508@pse-consulting.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support pgsql-admin

Donald Fraser wrote:

> 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.
>

Donald,

we discussed that this should go to pgsql-bugs, not any admin list.
While there might be some question if joining pg_proc to pg_type is
necessary for pgAdmin3, but it's certainly legal to query which pg_proc
is used for a type.
The underlying question for your case is whether it's allowed to create
overloaded functions of a function that's used for a type.
regprocin will not like this, so CREATE TYPE should deny creation if
input or output function isn't unique by name.

Regards,
Andreas

In response to

Browse pgadmin-support by date

  From Date Subject
Next Message George T. Gibson 2003-07-08 21:17:15 pgadmin III
Previous Message Dave Page 2003-07-08 20:49:05 Re: Overloading functions that are used by operators.

Browse pgsql-admin by date

  From Date Subject
Next Message Andrew Sullivan 2003-07-09 04:34:33 Re: Kill Process ERROR !!!
Previous Message Dave Page 2003-07-08 20:49:05 Re: Overloading functions that are used by operators.