Обсуждение: Re: [ADMIN] Overloading functions that are used by operators.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 10 July 2003 16:38
> To: Dave Page
> Cc: Donald Fraser; [ADMIN]; [pgADMIN]
> Subject: Re: [ADMIN] [pgadmin-support] Overloading functions
> that are used by operators.
>
>
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > Well I always thought you could overload functions in this way,
> > however with your example below, although it creates it OK,
> PostgreSQL
> > (not
> > pgAdmin) gives the error 'ERROR: There is more than one
> function named
> > textcat'.
>
> I see no such error here ...
>
> regression=# create function pg_catalog.textcat(varchar,text)
> regression-# returns text as 'textcat' language 'internal';
> CREATE FUNCTION

Andreas (Pflug) reckons it's gone in 7.4, but in 7.3.2 I see:

postgresql=# create function pg_catalog.textcat(varchar,text)
postgresql-# returns text as 'textcat' language 'internal';
CREATE FUNCTION
postgresql=# SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as
opowner, op.oprkind, op.oprcanhash,
postgresql-#                op.oprleft, op.oprright, lt.typname as
lefttype, rt.typname as righttype, et.typname as resulttype,
postgresql-#                co.oprname as compop, ne.oprname as negop,
lso.oprname as leftsortop, rso.oprname as rightsortop,
postgresql-#                lco.oprname as lscmpop, gco.oprname as
gtcmpop,
postgresql-#                po.proname as operproc, pj.proname as
joinproc, pr.proname as restrproc, description
postgresql-#           FROM pg_operator op
postgresql-#           LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
postgresql-#           LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
postgresql-#           JOIN pg_type et on et.oid=op.oprresult
postgresql-#           LEFT OUTER JOIN pg_operator co ON
co.oid=op.oprcom
postgresql-#           LEFT OUTER JOIN pg_operator ne ON
ne.oid=op.oprnegate
postgresql-#           LEFT OUTER JOIN pg_operator lso ON
lso.oid=op.oprlsortop
postgresql-#           LEFT OUTER JOIN pg_operator rso ON
rso.oid=op.oprrsortop
postgresql-#           LEFT OUTER JOIN pg_operator lco ON
lco.oid=op.oprltcmpop
postgresql-#           LEFT OUTER JOIN pg_operator gco ON
gco.oid=op.oprgtcmpop
postgresql-#           JOIN pg_proc po ON po.oid=op.oprcode
postgresql-#           LEFT OUTER JOIN pg_proc pr ON pr.oid=op.oprrest
postgresql-#           LEFT OUTER JOIN pg_proc pj ON pj.oid=op.oprjoin
postgresql-#           LEFT OUTER JOIN pg_description des ON
des.objoid=op.oid;
ERROR:  There is more than one procedure named textcat

Dropping the extra function, or changing the query a little fixes it:

postgresql=# SELECT op.oid, op.oprname, pg_get_userbyid(op.oprowner) as
opowner, op.oprkind, op.oprcanhash,
postgresql-#                op.oprleft, op.oprright, lt.typname as
lefttype, rt.typname as righttype, et.typname as resulttype,
postgresql-#                co.oprname as compop, ne.oprname as negop,
lso.oprname as leftsortop, rso.oprname as rightsortop,
postgresql-#                lco.oprname as lscmpop, gco.oprname as
gtcmpop,
postgresql-#                op.oprcode as operproc, op.oprjoin as
joinproc, op.oprrest as restrproc, description
postgresql-#           FROM pg_operator op
postgresql-#           LEFT OUTER JOIN pg_type lt ON lt.oid=op.oprleft
postgresql-#           LEFT OUTER JOIN pg_type rt ON rt.oid=op.oprright
postgresql-#           JOIN pg_type et on et.oid=op.oprresult
postgresql-#           LEFT OUTER JOIN pg_operator co ON
co.oid=op.oprcom
postgresql-#           LEFT OUTER JOIN pg_operator ne ON
ne.oid=op.oprnegate
postgresql-#           LEFT OUTER JOIN pg_operator lso ON
lso.oid=op.oprlsortop
postgresql-#           LEFT OUTER JOIN pg_operator rso ON
rso.oid=op.oprrsortop
postgresql-#           LEFT OUTER JOIN pg_operator lco ON
lco.oid=op.oprltcmpop
postgresql-#           LEFT OUTER JOIN pg_operator gco ON
gco.oid=op.oprgtcmpop
postgresql-#           LEFT OUTER JOIN pg_description des ON
des.objoid=op.oid;
 oid  | oprname | opowner  | oprkind | oprcanhash | oprleft | oprright |
lefttype ....

Regards, Dave.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Dave Page" <dpage@vale-housing.co.uk>
Cc: "Donald Fraser" <demolish@cwgsy.net>; "[ADMIN]"
<pgsql-admin@postgresql.org>; "[pgADMIN]" <pgadmin-support@postgresql.org>
Sent: Thursday, July 10, 2003 5:06 PM
Subject: Re: [ADMIN] [pgadmin-support] Overloading functions that are used by
operators.


> "Dave Page" <dpage@vale-housing.co.uk> writes:
> >> I see no such error here ...
> >>
> >> regression=# create function pg_catalog.textcat(varchar,text)
> >> regression-# returns text as 'textcat' language 'internal';
> >> CREATE FUNCTION
>
> > Andreas (Pflug) reckons it's gone in 7.4, but in 7.3.2 I see:
> > [ complicated query ]
>
> Oh.  I'll bet you're running into the regproc-reverse-conversion
> problem.  'textcat' is one of the histogram values appearing in pg_stats
> for pg_operator.oprcode, right?  The error comes when the planner tries
> to convert that string back to OID form.  We fixed this in 7.4 by
> modifying the way that pg_statistic stores data values, so that no
> conversion needs to occur.
>
> regards, tom lane

Well there you go then... trust me to run into some obscure bug!

I kept asking how it was possible to get the function oid via just the function
name, and nobody pointed out what the undelying data type of
pg_operator.oprcode is.

In that case you can use the subselect approach that I originally suggested and
you will find that it actually works.
That is replace column 'po.proname as operproc,' with '(SELECT po.proname FROM
pg_proc po WHERE po.oid=op.oprcode) as operproc,' and obviously remove the
associated join statment and repeat this for the other two columns: pj.proname
as joinproc and pr.proname as restrproc.

Regards
Donald Fraser.

"Dave Page" <dpage@vale-housing.co.uk> writes:
>> I see no such error here ...
>>
>> regression=# create function pg_catalog.textcat(varchar,text)
>> regression-# returns text as 'textcat' language 'internal';
>> CREATE FUNCTION

> Andreas (Pflug) reckons it's gone in 7.4, but in 7.3.2 I see:
> [ complicated query ]

Oh.  I'll bet you're running into the regproc-reverse-conversion
problem.  'textcat' is one of the histogram values appearing in pg_stats
for pg_operator.oprcode, right?  The error comes when the planner tries
to convert that string back to OID form.  We fixed this in 7.4 by
modifying the way that pg_statistic stores data values, so that no
conversion needs to occur.

            regards, tom lane