--- 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
