Обсуждение: 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_namespacen 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_namespacen 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 pLEFT 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.Seifert@gmx.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