Release 8.0.0.0beta3

I tried to use regclass() in a plpgsql function to derive a tablename
from its oid so as to build a command string, but I am unable to use the
value returned because it cannot be cast to anything.  Therefore I will
have to use a complex query on the catalog to do the same work.

This seems overly restrictive.  Would there be a problem in allowing
regclass() to be cast to text?

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Let no man say when he is tempted, I am tempted of 
      God; for God cannot be tempted with evil, neither 
      tempteth he any man; But every man is tempted, when he
      is drawn away of his own lust, and enticed."          
                                       James 1:13,14 


Sat, 09 Oct 2004 01:25:01 +0100
Oliver Elphick <olly@lfix.co.uk>
Inability to cast regclass is too restrictive
Oliver Elphick wrote:
> I tried to use regclass() in a plpgsql function to derive a tablename
> from its oid so as to build a command string, but I am unable to use the
> value returned because it cannot be cast to anything.  Therefore I will
> have to use a complex query on the catalog to do the same work.
> 
> This seems overly restrictive.  Would there be a problem in allowing
> regclass() to be cast to text?
> 

I agree (I've been frustrated by this myself before), but for a 
workaround, see the following:

create or replace function any2text(anyelement) returns text as'
begin
  return $1;
end
' language plpgsql;

select any2text(1255::oid::regclass) || ' is the relname in text';
             ?column?
--------------------------------
  pg_proc is the relname in text
(1 row)

HTH,

Joe

Fri, 08 Oct 2004 20:13:45 -0700
Joe Conway <mail@joeconway.com>
Re: Inability to cast regclass is too restrictive
Oliver Elphick <olly@lfix.co.uk> writes:
> I tried to use regclass() in a plpgsql function to derive a tablename
> from its oid so as to build a command string, but I am unable to use the
> value returned because it cannot be cast to anything.  Therefore I will
> have to use a complex query on the catalog to do the same work.

Hmm?  plpgsql is about as permissive as you can get on this point.
Just assign the result to a variable of the desired type, and it will
do it if the textual representations are at all compatible.  Example:

regression=# create function foo(oid) returns text as '
regression'# declare z text;
regression'# begin
regression'#   z := $1::regclass;
regression'#   return z;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select 'pg_proc'::regclass::oid;
 oid  
------
 1255
(1 row)

regression=# select foo(1255);               
   foo   
---------
 pg_proc
(1 row)

> This seems overly restrictive.  Would there be a problem in allowing
> regclass() to be cast to text?

I'm on record that we should allow (explicit) casting to and from text
for all types, using the types' I/O functions to implement it.  But
plpgsql already provides essentially that mechanism in its assignment
operations.  You just hafta do the explicit assignment...

			regards, tom lane

Sat, 09 Oct 2004 00:35:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Inability to cast regclass is too restrictive 
On Sat, 2004-10-09 at 05:35, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > I tried to use regclass() in a plpgsql function to derive a tablename
> > from its oid so as to build a command string, but I am unable to use the
> > value returned because it cannot be cast to anything.  Therefore I will
> > have to use a complex query on the catalog to do the same work.
> 
> Hmm?  plpgsql is about as permissive as you can get on this point.
> Just assign the result to a variable of the desired type, and it will
> do it if the textual representations are at all compatible.  Example:
> 
> regression=# create function foo(oid) returns text as '
...
> 
> I'm on record that we should allow (explicit) casting to and from text
> for all types, using the types' I/O functions to implement it.  But
> plpgsql already provides essentially that mechanism in its assignment
> operations.  You just hafta do the explicit assignment...

Thanks for the example.  I was trying to do 

  cmd = ''SELECT * FROM '' || regclass(someoid);

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "Every good gift and every perfect gift is from above, 
      and cometh down from the Father of lights, with whom 
      is no variableness, neither shadow of turning."       
                                   James 1:17 


Sat, 09 Oct 2004 07:18:56 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: Inability to cast regclass is too restrictive