[Firebird-devel] Useful SQL Stored Procedures as part of standard firebird installation

[Slavomir S. <sko...@el...>]

Hi,

will be possible to extend firebird installation by SQL stored 
procedures that will solve common problems?

It will be like UDF (let say in directory SQL), and every one can 
include/use this SQL like fbudf.

Examples of useful:
SP that returns complete metadata for another SP.
SP that set NULL/NOT NULL for column and works on FB2.5 and FB3.
Some examples from our development:

-- Drop Primary Key for given table
CREATE OR ALTER PROCEDURE MASA$DropPrimaryKey(Relation RDB$Relation_Name)
AS
DECLARE VARIABLE cn VARCHAR(500)=NULL;
BEGIN
   SELECT TRIM(rc.rdb$constraint_name)
     FROM rdb$Relation_Constraints RC LEFT JOIN RDB$Indices I ON 
RC.rdb$Index_Name=I.rdb$Index_Name
     WHERE I.rdb$Relation_Name=:Relation AND 
RC.rdb$Constraint_Type='PRIMARY KEY'
     INTO :cn;
   IF (cn IS NOT NULL) THEN BEGIN
     SQL='ALTER TABLE ' || TRIM(Relation) ||' DROP CONSTRAINT '||cn||';';
     EXECUTE STATEMENT SQL;
   END
END

Slavek

-- 
Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-----------------------------------------------------------------
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---------------------------------------------------------------
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:sko...@el...
http://www.elektlabs.cz