--- In firebird-support@yahoogroups.com, "camalot_todd" wrote:

>
>
>
> --- In firebird-support@yahoogroups.com, "Leyne, Sean" wrote:
> >
> >
> > > > I would like to add a script that does a "DROP EXTERNAL FUNCTION SQRT".
> > > >
> > > > The problem I have is that some of the databases that will run this script
> > > already have the UDF dropped and so the script errors.
> > > >
> > > > Is there a way to run statements like DROP INDEX, DROP EXTERNAL
> > > FUNCTION, etc. in a script without generating an error of the object isn't
> > > found?
> > >
> > > In a pure SQL script, I wouldn't know.
> >
> > This should do:
> >
> > EXECUTE BLOCK
> > AS
> > BEGIN
> > IF (EXISTS (SELECT 1 FROM RDB$Functions WHERE RDB$FunctionName = 'SQRT') ) THEN
> > EXECUTE STATEMENT 'DROP EXTERNAL FUNCTION SQRT';
> >
> > END
> >
> >
> >
> > Sean
> >
>
> Thanks Sean Worked great! Just had to surround the statements with SET TERM and fix the column name (RDB$FUNCTION_NAME).
>
> This will allow us to clean up a bunch of things in the database.
>
> Todd
>

We no longer have a need for UDF's because the functions we used are now part of Firebird (we have clients using 2.1 and 2.5). So it is pretty cool that I can drop all UDF's with the code below.

SET TERM ^ ;

EXECUTE BLOCK

AS
declare variable RDB$FUNCTION_NAME CHAR(31);
BEGIN
FOR SELECT RDB$FUNCTION_NAME FROM RDB$Functions WHERE RDB$FUNCTION_NAME <> 'LOWER' INTO :RDB$Function_Name
DO
EXECUTE STATEMENT 'DROP EXTERNAL FUNCTION '||:RDB$Function_Name;

END
^

SET TERM ; ^


Didn't think it would work to concatenate the partial statement with a variable name to EXECUTE the drop.

Anyhow I found I can't drop the UDF called LOWER. Gives me an error (Token Unknown) I believe because LOWER is now a function in Firebird.

Anyone know how to drop the UDF called LOWER?

Thanks

Todd