Re: [ADMIN] Overloading functions that are used by operators.

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