text cast on regprocedure fails on 8.2

Hello,

for dropping all functions within the current schema I use this SQL query:

SELECT DISTINCT 'DROP FUNCTION ' || p.oid::regprocedure::text || ' CASCADE;' FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = current_schema();

It works fine on 8.3. It fails on 8.2 with the error message that conversion to text is not possible for data type regprocedure.

I didn't see any explicit type cast in 8.3 for regprocedure -> text. Is there a way to add it in 8.2?

Thank you very much,

Peter
--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

Jan-Peter Seifert wrote:
> Hello,
>
> for dropping all functions within the current schema I use this SQL query:
>
> SELECT DISTINCT 'DROP FUNCTION ' || p.oid::regprocedure::text || ' CASCADE;' FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = current_schema();
>
> It works fine on 8.3. It fails on 8.2 with the error message that conversion to text is not possible for data type regprocedure.
>
> I didn't see any explicit type cast in 8.3 for regprocedure -> text. Is there a way to add it in 8.2?

You can do this:

SELECT DISTINCT 'DROP FUNCTION ' || textin(regprocedureout(p.oid::regprocedure)) || ' CASCADE;' FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = current_schema();

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Jan-Peter Seifert" <Jan-Peter(dot)Seifert(at)gmx(dot)de> writes:
> I didn't see any explicit type cast in 8.3 for regprocedure -> text. Is there a way to add it in 8.2?

You can always cast pretty much anything to anything via a variable
assignment in plpgsql...

regards, tom lane

Hello Alvaro, Hello Tom,

thank you very much for your quick and helpful replies.

> You can always cast pretty much anything to anything via a variable
> assignment in plpgsql...

<thud> I only tried it in SQL. So the function works just fine after the removal of the text cast on 8.2 as well.

For SQL I'll stick with "textin(regprocedureout(p.oid::regprocedure))".

Peter

--
Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 -
sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser