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

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

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