Overloaded Stored Procedures
Tom Girsch asked for a script to drop all overloaded versions of a stored procedure. Paul Watson said this had been an RFE for at least 15 years. Gabor Fekete posted an SPL procedure using exec to run generated DROP statements, and Art Kagel suggested myschema with --drop-proc. Tom wrote his own SQL that builds DROP statements from sysprocedures.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Does anyone here have a handy-dandy script that will drop all versions of an overloaded stored procedure?
e.g.,
DROP PROCEDURE myproc(CHAR);
DROP PROCEDURE myproc(INT);
DROP PROCEDURE myproc(CHAR,INT);------------------------------
TOM GIRSCH
------------------------------
#Informix
This has been a RFE for years, at least 15 now
Hi Tom
We have a SPL solution for that. If you can use SPL and you have the exec datablade here is our solution.
Create dba procedure drop_routine(_procname like sysprocedures.procname)Define sqlstmt lvarchar(2048);
Define result varchar(255);
Foreach 'drop '|| decode (isproc, 'f', 'function ', 'procedure ') || ' ' || procname || '( '|| paramtypes::lvarchar || '); '
Into sqlstmt
From sysprocedures
Where procname = _procname
Let result =exec(sqlstmt);
End foreach;
End procedure;
------------------------------
Gábor Fekete
Software Engineer
Capital Systems
Budapest
------------------------------
Tom: myschema -d somedb -f procname --drop-proc |fgrep DROP >drop.procs Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Here's what I came up with:
SELECT procname, isproc, paramtypes::LVARCHAR AS paramtypes
FROM sysprocedures
WHERE [whatever conditions]
INTO TEMP proctemp WITH NO LOG;
UNLOAD TO "dropspatialproc.sql" DELIMITER ";"uSELECT '{ ' || TRIM(procname) || '-' || (1000 + LENGTH(paramtypes))::LVARCHAR || ' } ' ||
'DROP ' || CASE WHEN isproc = 'f' THEN 'FUNCTION' ELSE 'PROCEDURE' END ||
' IF EXISTS ' || TRIM(procname) ||
CASE WHEN (LENGTH(paramtypes) > 0) THEN '(' || TRIM(paramtypes) || ')' ELSE '' END
FROM proctemp
ORDER BY 1 DESC;
------------------------------
TOM GIRSCH
------------------------------