Skip site navigation (1) Skip section navigation (2)

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

From: "Donald Fraser" <demolish(at)cwgsy(dot)net>
To: "[pgADMIN]" <pgadmin-support(at)postgresql(dot)org>
Subject: Re: [ADMIN] Overloading functions that are used by operators.
Date: 2003-07-10 17:10:52
Message-ID: 00b601c34706$3b7ba280$1664a8c0@DEMOLITION (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-admin
----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
Cc: "Donald Fraser" <demolish(at)cwgsy(dot)net>; "[ADMIN]"
<pgsql-admin(at)postgresql(dot)org>; "[pgADMIN]" <pgadmin-support(at)postgresql(dot)org>
Sent: Thursday, July 10, 2003 5:06 PM
Subject: Re: [ADMIN] [pgadmin-support] Overloading functions that are used by
operators.


> "Dave Page" <dpage(at)vale-housing(dot)co(dot)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.


In response to

pgsql-admin by date

Next:From: Zeno R.R. DavatzDate: 2003-07-11 05:58:59
Subject: corrupt data
Previous:From: Dave PageDate: 2003-07-10 16:30:17
Subject: Re: [ADMIN] Overloading functions that are used by operators.

pgadmin-support by date

Next:From: George T. GibsonDate: 2003-07-10 19:33:03
Subject: pgadminIII bug
Previous:From: Dave PageDate: 2003-07-10 16:30:17
Subject: Re: [ADMIN] Overloading functions that are used by operators.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group