namespace SqlDynamite.Common

open System
open System.Data

type PostgreSqlMetadataFinder() =
    inherit MetadataFinder()

    static let system_schemas = "' '"(*"'pg_catalog','information_schema'"*)
    
    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let result = "CREATE TABLE " + tablename + " ("
        let rowdef index =
            let record = fields.Rows.Item(index)
            let part1 = "\n\t" + record.Item("column_name").ToString() + " "
            let rtn = record.Item("type_name").ToString()
            let part2 =
                match rtn.ToLower() with
                | "char" | "character varying" ->
                    rtn + " (" + record.Item("length").ToString().Trim() + ") "
                | "decimal" | "numeric" ->
                    rtn + " (" + record.Item("precision").ToString().Trim() + ", " + record.Item("scale").ToString().Trim() + ") "
                | "user-defined" -> record.Item("udt_name").ToString().Trim() + " "
                | _ -> rtn + " "
            let nullable = record.Item("nullable").ToString().Trim()
            let nulldef = if nullable = "1" then "NULL" else "NOT NULL"
            let dsource = record.Item("dsource")
            let defdef = if dsource :? DBNull then "" else " DEFAULT " + dsource.ToString().Trim()
            part1 + part2 + nulldef + defdef + ","
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (rowdef(n - 1) :: acc)
            else
                acc
        result + String.Join("", loop fields.Rows.Count []) + Environment.NewLine + ")"

    member this.ComposeTableType(typename:string, fields:DataTable) : string =
        let result = "CREATE TYPE " + typename + " AS ("
        let rowdef index =
            let record = fields.Rows.Item(index)
            "\n\t" + record.Item("fieldname").ToString() + " " + record.Item("fieldtypename").ToString() + " "
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (rowdef(n - 1) :: acc)
            else
                acc
        result + String.Join("", loop fields.Rows.Count []) + Environment.NewLine + ")"

    member this.GetParameters(name:string) : string list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let objName = parts.[1]
        let query = "select p.parameter_mode, p.parameter_name, case p.data_type when 'USER-DEFINED' then p.udt_name else p.data_type end as data_type, p.parameter_default from information_schema.routines as r
                     inner join information_schema.parameters as p on p.specific_name = r.specific_name
                     where r.routine_name = '" + objName + "' and r.routine_schema = '" + schema + "' order by p.dtd_identifier :: int;"
        let lst = this.CreateDataSet(query).Tables.Item(0)
        let rec loop n acc =
            if n > 0 then
                let pmode = lst.Rows.Item(n - 1).Item("parameter_mode").ToString()
                let pname = lst.Rows.Item(n - 1).Item("parameter_name").ToString()
                let ptype = lst.Rows.Item(n - 1).Item("data_type").ToString()
                let pdeft = (if lst.Rows.Item(n - 1).Item("parameter_default") :? DBNull then "" else " DEFAULT " + lst.Rows.Item(n - 1).Item(3).ToString())
                loop (n - 1) ("\n\t" + pmode + " \"" + pname + "\" " + ptype + pdeft :: acc)
            else
                acc
        loop lst.Rows.Count []
    
    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let tblName = parts.[1]
        let script = String.Format("select column_name, is_nullable nullable, character_maximum_length as length, numeric_precision as precision, numeric_scale as scale, data_type as type_name, column_default as dsource, udt_name from information_schema.columns where table_schema = '{0}' and table_name = '{1}' order by ordinal_position;", schema, tblName)
        let columns = this.CreateDataSet(script).Tables.Item(0)
        [columns; null; null]

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let objName = parts.[1]
        let query =
            if objtype = ObjectType.TRIGGER then
                "select s.ACTION_STATEMENT from INFORMATION_SCHEMA.TRIGGERS s where s.TRIGGER_SCHEMA = '" + schema + "' and s.TRIGGER_NAME = '"
            elif objtype = ObjectType.VIEW then
                "select s.VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS s where s.TABLE_SCHEMA = '" + schema + "' and s.TABLE_NAME = '"
            elif objtype = ObjectType.FUNCTION || objtype = ObjectType.PROCEDURE then
                "select s.ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES s where s.ROUTINE_SCHEMA = '" + schema + "' and s.ROUTINE_NAME = '"
            elif objtype = ObjectType.WINDOW_FUNCTION then
                "select s.ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES s where s.ROUTINE_SCHEMA = '" + schema + "' and s.ROUTINE_NAME = '"
            else ""
        let lst = this.CreateDataSet(query + objName + "'").Tables.Item(0)
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (lst.Rows.Item(n - 1).Item(0).ToString() :: acc)
            else
                acc
        if objtype = ObjectType.FUNCTION || objtype = ObjectType.WINDOW_FUNCTION || objtype = ObjectType.PROCEDURE then
            if lst.Rows.Count > 0 && not (lst.Rows.Item(0).Item(0) :? DBNull) then 
                let data = this.CreateDataSet("select s.EXTERNAL_LANGUAGE, s.DATA_TYPE, p.PROLEAKPROOF, p.PROISSTRICT, p.PROVOLATILE, p.PROPARALLEL, p.PROCOST, p.PROROWS, p.PRORETSET, p.PROSUPPORT :: varchar
                                               from INFORMATION_SCHEMA.ROUTINES s inner join pg_proc p on p.proname = s.routine_name inner join pg_namespace as n on n.oid = p.pronamespace and n.nspname = s.ROUTINE_SCHEMA
                                               where s.ROUTINE_SCHEMA = '" + schema + "' and s.ROUTINE_NAME = '" + objName + "'").Tables.Item(0).Rows.Item(0)
                let header =
                    if objtype = ObjectType.FUNCTION || objtype = ObjectType.WINDOW_FUNCTION then
                        let is_window = if objtype = ObjectType.WINDOW_FUNCTION then "\n WINDOW" else ""
                        let is_leakproof = if data.Item("PROLEAKPROOF") = true then "\n LEAKPROOF" else ""
                        let is_strict = if data.Item("PROISSTRICT") = true then "\n STRICT" else ""
                        let volkind = if data.Item("PROVOLATILE") = 'i' then "\n IMMUTABLE" elif data.Item("PROVOLATILE") = 's' then "\n STABLE" elif data.Item("PROVOLATILE") = 'v' then "\n VOLATILE" else ""
                        let parkind = if data.Item("PROPARALLEL") = 's' then "\n PARALLEL SAFE" elif data.Item("PROPARALLEL") = 'r' then "\n PARALLEL RESTRICTED" elif data.Item("PROPARALLEL") = 'u' then "\n PARALLEL UNSAFE" else ""
                        let support = if data.Item("PROSUPPORT") = "-" then "" else "\n SUPPORT " + data.Item("PROSUPPORT").ToString().Trim()
                        let retset = if data.Item("PRORETSET") = true then "SETOF " else ""
                        let cost = "\n COST " + data.Item("PROCOST").ToString().Trim()
                        let rows = if data.Item("PRORETSET") = true then "\n ROWS " + data.Item("PROROWS").ToString().Trim() else ""
                        "CREATE OR REPLACE FUNCTION " + name + "(" + String.Join("", this.GetParameters(name)) + ")" + "\n RETURNS " + retset + data.Item("DATA_TYPE").ToString() + "\n LANGUAGE " + data.Item("EXTERNAL_LANGUAGE").ToString() + volkind + is_window + is_leakproof + is_strict + parkind + cost + rows + support
                    else
                        "CREATE OR REPLACE " + objtype.ToString() + " " + name + "(" + String.Join("", this.GetParameters(name)) + ")" + "\n LANGUAGE " + data.Item("EXTERNAL_LANGUAGE").ToString()
                let body = "\nAS $" + (if objtype = ObjectType.WINDOW_FUNCTION then "function" else objtype.ToString().ToLower()) + "$\n" + String.Join("", loop lst.Rows.Count [])
                let footer = "\n$" + (if objtype = ObjectType.WINDOW_FUNCTION then "function" else objtype.ToString().ToLower()) + "$;"
                [header;body;footer]
            else
                let prosrc = this.CreateDataSet("select s.PROSRC from PG_CATALOG.PG_PROC s INNER JOIN PG_CATALOG.PG_NAMESPACE n on n.OID = s.PRONAMESPACE where n.NSPNAME = '" + schema + "' and s.PRONAME = '" + objName + "'").Tables.Item(0).Rows.Item(0).Item(0).ToString()
                [prosrc]
        else
            loop lst.Rows.Count []

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        let rec loop n (acc:string list) =
            if n > 0 then
                let un = if String.Join("",acc).Length > 0 && n < types.Length then " union all " else ""
                if caseSensitive then
                    if types.Item(n - 1) = ObjectType.PROCEDURE then
                        loop (n - 1) (String.Format("select ROUTINE_SCHEMA || '.' || ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_CATALOG = '" + this._connection.Database + "' and ROUTINE_SCHEMA not in (" + system_schemas + ") and ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select TRIGGER_SCHEMA || '.' || TRIGGER_NAME as name, 'TRIGGER' as type from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_CATALOG = '" + this._connection.Database + "' and TRIGGER_SCHEMA not in (" + system_schemas + ") and ACTION_STATEMENT like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select TABLE_SCHEMA || '.' || TABLE_NAME as name, 'VIEW' as type from INFORMATION_SCHEMA.VIEWS where TABLE_CATALOG = '" + this._connection.Database + "' and TABLE_SCHEMA not in (" + system_schemas + ") and VIEW_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select ROUTINE_SCHEMA || '.' || ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_CATALOG = '" + this._connection.Database + "' and ROUTINE_SCHEMA not in (" + system_schemas + ") and ROUTINE_TYPE = 'FUNCTION' and ROUTINE_DEFINITION like '%{0}%'" + un, searchStr) :: acc)
                    else
                        loop (n - 1) acc
                else
                    if types.Item(n - 1) = ObjectType.PROCEDURE then
                        loop (n - 1) (String.Format("select ROUTINE_SCHEMA || '.' || ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_CATALOG = '" + this._connection.Database + "' and ROUTINE_SCHEMA not in (" + system_schemas + ") and ROUTINE_TYPE = 'PROCEDURE' and lower(ROUTINE_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.TRIGGER then
                        loop (n - 1) (String.Format("select TRIGGER_SCHEMA || '.' || TRIGGER_NAME as name, 'TRIGGER' as type from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_CATALOG = '" + this._connection.Database + "' and TRIGGER_SCHEMA not in (" + system_schemas + ") and lower(ACTION_STATEMENT) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.VIEW then
                        loop (n - 1) (String.Format("select TABLE_SCHEMA || '.' || TABLE_NAME as name, 'VIEW' as type from INFORMATION_SCHEMA.VIEWS where TABLE_CATALOG = '" + this._connection.Database + "' and TABLE_SCHEMA not in (" + system_schemas + ") and lower(VIEW_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    elif types.Item(n - 1) = ObjectType.FUNCTION then
                        loop (n - 1) (String.Format("select ROUTINE_SCHEMA || '.' || ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_CATALOG = '" + this._connection.Database + "' and ROUTINE_SCHEMA not in (" + system_schemas + ") and ROUTINE_TYPE = 'FUNCTION' and lower(ROUTINE_DEFINITION) like '%{0}%'" + un, searchStr.ToLower()) :: acc)
                    else
                        loop (n - 1) acc
            else acc
        String.Join("",loop types.Length [])

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let startQuery = "select name, type from ("
        let rec loop n (acc:string list) =
            if n > 0 then
                let un = if String.Join("",acc).Length <> startQuery.Length && n < types.Length then " union all " else ""
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) ("select ROUTINE_SCHEMA || '.' || ROUTINE_NAME as name, ROUTINE_TYPE as type from INFORMATION_SCHEMA.ROUTINES where ROUTINE_CATALOG = '" + this._connection.Database + "' and ROUTINE_SCHEMA not in (" + system_schemas + ") and ROUTINE_TYPE = 'PROCEDURE'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) ("select TRIGGER_SCHEMA || '.' || TRIGGER_NAME as name, 'TRIGGER' as type from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_CATALOG = '" + this._connection.Database + "' and TRIGGER_SCHEMA not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || TABLE_NAME as name, 'VIEW' as type from INFORMATION_SCHEMA.VIEWS where TABLE_CATALOG = '" + this._connection.Database + "' and TABLE_SCHEMA not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) ("select ROUTINE_SCHEMA || '.' || ROUTINE_NAME as name, (CASE WHEN ROUTINE_TYPE IS NOT NULL THEN ROUTINE_TYPE ELSE CASE PROKIND WHEN 'w' THEN 'WINDOW FUNCTION' WHEN 'a' THEN 'AGGREGATE' WHEN 'p' THEN 'PROCEDURE' WHEN 'f' THEN 'FUNCTION' ELSE 'UNKNOWN' END END) as type
                                   from INFORMATION_SCHEMA.ROUTINES r inner join pg_proc p on p.proname = r.routine_name inner join pg_namespace as n on n.oid = p.pronamespace and n.nspname = r.ROUTINE_SCHEMA
                                   where ROUTINE_CATALOG = '" + this._connection.Database + "' and ROUTINE_SCHEMA not in (" + system_schemas + ") and (ROUTINE_TYPE = 'FUNCTION' OR ROUTINE_TYPE IS NULL)" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || TABLE_NAME as name, TABLE_TYPE as type from INFORMATION_SCHEMA.TABLES where TABLE_CATALOG = '" + this._connection.Database + "' and TABLE_SCHEMA not in (" + system_schemas + ") and TABLE_TYPE = 'BASE TABLE'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || CONSTRAINT_NAME as name, CONSTRAINT_TYPE as type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_CATALOG = '" + this._connection.Database + "' and TABLE_SCHEMA not in (" + system_schemas + ") and CONSTRAINT_TYPE = 'FOREIGN KEY'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || CONSTRAINT_NAME as name, CONSTRAINT_TYPE as type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_CATALOG = '" + this._connection.Database + "' and TABLE_SCHEMA not in (" + system_schemas + ") and CONSTRAINT_TYPE = 'PRIMARY KEY'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) ("select TABLE_SCHEMA || '.' || CONSTRAINT_NAME as name, CONSTRAINT_TYPE as type from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_CATALOG = '" + this._connection.Database + "' and TABLE_SCHEMA not in (" + system_schemas + ") and CONSTRAINT_TYPE = 'UNIQUE'" + un :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) ("select SEQUENCE_SCHEMA || '.' || SEQUENCE_NAME as name, 'SEQUENCE' as type from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_CATALOG = '" + this._connection.Database + "' and SEQUENCE_SCHEMA not in (" + system_schemas + ")" + un :: acc)
                elif types.Item(n - 1) = ObjectType.TYPE then
                    loop (n - 1) ("select USER_DEFINED_TYPE_SCHEMA || '.' || USER_DEFINED_TYPE_NAME as name, 'TYPE' as type from INFORMATION_SCHEMA.USER_DEFINED_TYPES where USER_DEFINED_TYPE_CATALOG = '" + this._connection.Database + "' and USER_DEFINED_TYPE_SCHEMA not in (" + system_schemas + ")" + un :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let query = String.Join("",loop types.Length [])
        let cond =
            if caseSensitive then
                ") as tbl where name like '%" + search + "%' order by type, name;"
            else
                ") as tbl where lower(name) like '%" + search.ToLower() + "%' order by type, name;"
        startQuery + query + cond

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query = "select distinct t.TABLE_SCHEMA || '.' || t.TABLE_NAME as name, TABLE_TYPE from INFORMATION_SCHEMA.TABLES as t inner join INFORMATION_SCHEMA.COLUMNS as c on t.TABLE_NAME = c.TABLE_NAME"
        let cond =
            if caseSensitive then
                " where t.TABLE_CATALOG = '" + this._connection.Database + "' and t.TABLE_SCHEMA not in (" + system_schemas + ") and TABLE_TYPE = 'BASE TABLE' and COLUMN_NAME like '%" + search
            else
                " where t.TABLE_CATALOG = '" + this._connection.Database + "' and t.TABLE_SCHEMA not in (" + system_schemas + ") and TABLE_TYPE = 'BASE TABLE' and lower(COLUMN_NAME) like '%" + search.ToLower()
        query + cond + "%' order by name"

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        raise (NotImplementedException())

    override this.GetPrimaryKeyDefinition(constraintName:string) : string =
        raise (NotImplementedException())

    override this.GetIndexDefinition(constraintName:string) : string =
        raise (NotImplementedException())

    override this.GetSequenceDefinition(sequenceName:string) : string =
        let parts = sequenceName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select data_type,start_value,increment,minimum_value,maximum_value,cycle_option from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = '{0}' and SEQUENCE_NAME='{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE SEQUENCE " + sequenceName + " INCREMENT " + row.Item("increment").ToString()
        let part2 = " MINVALUE " + row.Item("minimum_value").ToString() + " MAXVALUE " + row.Item("maximum_value").ToString() + " START " + row.Item("start_value").ToString()
        let part3 = if row.Item("cycle_option").ToString() = "YES" then " CYCLE" else " NO CYCLE"
        part1 + part2 + part3

    override this.GetSynonymDefinition(synonymName:string) : string =
        raise (NotImplementedException())

    override this.GetTypeDefinition(typeName:string) : string =
        let parts = typeName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("SELECT a.attname AS fieldname, at.typname AS fieldtypename, a.attnum AS fieldorder
            FROM pg_catalog.pg_namespace ns
            INNER JOIN pg_catalog.pg_type t ON ns.oid = t.typnamespace
            INNER JOIN pg_class c ON c.oid = t.typrelid
            INNER JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
            INNER JOIN pg_catalog.pg_type at ON a.atttypid = at.oid
            WHERE ns.nspname = '{0}' AND t.typname = '{1}' ORDER BY fieldorder", schema, name)
        this.ComposeTableType(typeName, this.CreateDataSet(query).Tables.Item(0))

    override this.GetJobDefinition(jobName:string) : string =
        raise (NotImplementedException())

    override this.GetReportDefinition(reportName:string) : string =
        raise (NotImplementedException())

    member this.ComposeAggregate(procName:string, data:DataRow) =
        let pms = String.Join(", ", this.GetParameters(procName)).Replace("\"\" ", "").Replace("IN ", "").Replace("\n", "").Replace("\t", "")
        let orderby = if data.Item("aggkind") <> 'n' then " ORDER BY " + pms else ""
        let variadic = if data.Item("aggkind") = 'h' then "VARIADIC " else ""
        let header = "CREATE OR REPLACE AGGREGATE " + procName + "(" + variadic + pms + orderby + ")\n(\n" 
        let sfunc = "\tSFUNC = " + data.Item("aggtransfn").ToString().Trim() + ",\n"
        let stypename = if data.Item("typcategory") = 'A' then data.Item("atypname").ToString().Trim() + "[]" else data.Item("typname").ToString().Trim()
        let stype = "\tSTYPE = " + stypename + ",\n"
        let sspace = if data.Item("aggtransspace") <> 0 then "\tSSPACE " + data.Item("aggtransspace").ToString().Trim() + ",\n" else ""
        let finalfunc = if data.Item("aggfinalfn") = "-" then "" else "\tFINALFUNC = " + data.Item("aggfinalfn").ToString().Trim() + ",\n"
        let finalfuncextra = if data.Item("aggfinalextra") = true then "\tFINALFUNC_EXTRA,\n" else ""
        let finalfunc_modify = "\tFINALFUNC_MODIFY = " + (if data.Item("aggfinalmodify") = 'r' then "READ_ONLY" elif data.Item("aggfinalmodify") = 's' then "SHAREABLE" elif data.Item("aggfinalmodify") = 'w' then "READ_WRITE" else "") + ",\n"
        let combinefunc = if data.Item("aggcombinefn") = "-" then "" else "\tCOMBINEFUNC = " + data.Item("aggcombinefn").ToString().Trim() + ",\n"
        let serialfunc = if data.Item("aggserialfn") = "-" then "" else "\tSERIALFUNC = " + data.Item("aggserialfn").ToString().Trim() + ",\n"
        let deserialfunc = if data.Item("aggdeserialfn") = "-" then "" else "\tDESERIALEFUNC = " + data.Item("aggdeserialfn").ToString().Trim() + ",\n"
        let initcond = if data.Item("agginitval") :? DBNull then "" else "\tINITCOND = '" + data.Item("agginitval").ToString().Trim() + "',\n"
        let msfunc = if data.Item("aggmtransfn") = "-" then "" else "\tMSFUNC = " + data.Item("aggmtransfn").ToString().Trim() + ",\n"
        let minvfunc = if data.Item("aggminvtransfn") = "-" then "" else "\tMINVFUNC = " + data.Item("aggminvtransfn").ToString().Trim() + ",\n"
        let mstypename = if data.Item("mtypcategory") = 'A' then data.Item("matypname").ToString().Trim() + "[]" else data.Item("mtypname").ToString().Trim()
        let mstype = if data.Item("mtypname") :? DBNull then "" else "\tMSTYPE = " + mstypename + ",\n"
        let msspace = if data.Item("aggmtransspace") <> 0 then "\tMSSPACE " + data.Item("aggmtransspace").ToString().Trim() + ",\n" else ""
        let mfinalfunc = if data.Item("aggmfinalfn") = "-" then "" else "\tMFINALFUNC = " + data.Item("aggmfinalfn").ToString().Trim() + ",\n"
        let mfinalfuncextra = if data.Item("aggmfinalextra") = true then "\tMFINALFUNC_EXTRA,\n" else ""
        let mfinalfunc_modify = "\tMFINALFUNC_MODIFY = " + (if data.Item("aggmfinalmodify") = 'r' then "READ_ONLY" elif data.Item("aggmfinalmodify") = 's' then "SHAREABLE" elif data.Item("aggmfinalmodify") = 'w' then "READ_WRITE" else "") + ",\n"
        let minitcond = if data.Item("aggminitval") :? DBNull then "" else "\tMINITCOND = '" + data.Item("aggminitval").ToString().Trim() + ",'\n"
        let sortop = if data.Item("aggsortop") <> 0u then "\tSORTOP = " + data.Item("oprname").ToString().Trim() + "\n" else ""
        header + sfunc + stype + sspace + finalfunc + finalfuncextra + finalfunc_modify + combinefunc + serialfunc + deserialfunc + initcond + msfunc + minvfunc + mstype + msspace + mfinalfunc + mfinalfuncextra + mfinalfunc_modify + minitcond + sortop + ");"
    
    override this.GetExtendedProcedureDefinition(procName:string) : string =
        let parts = procName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let script = "select n.nspname, b.proname, a.aggkind, a.aggtransfn :: varchar, a.aggfinalfn :: varchar,
                             a.aggcombinefn :: varchar, a.aggserialfn :: varchar, a.aggdeserialfn :: varchar,
                             a.aggmtransfn :: varchar, a.aggminvtransfn :: varchar, a.aggmfinalfn :: varchar,
                             a.aggtransspace, a.aggmtransspace, a.agginitval, a.aggminitval,
                             t.typname, mt.typname as mtypname, a.aggfinalmodify, a.aggmfinalmodify,
                             a.aggfinalextra, a.aggmfinalextra, a.aggsortop, o.oprname,
                             t.typcategory, mt.typcategory as mtypcategory,
                             ta.typname as atypname, mta.typname as matypname
                      from pg_catalog.pg_aggregate as a
                      inner join pg_catalog.pg_proc as b on b.oid = a.aggfnoid
                      inner join pg_catalog.pg_namespace as n on n.oid = b.pronamespace
                      inner join pg_catalog.pg_type as t on t.oid = a.aggtranstype
                      left  join pg_catalog.pg_type as mt on mt.oid = a.aggmtranstype
                      left  join pg_catalog.pg_type as ta on ta.oid = t.typelem
                      left  join pg_catalog.pg_type as mta on mta.oid = mt.typelem
                      left  join pg_catalog.pg_operator as o on o.oid = a.aggsortop
                      where n.nspname = '" + schema + "' and b.proname = '" + name + "';"
        let data = this.CreateDataSet(script).Tables.Item(0).Rows
        let rec loop n acc =
            if n > 0 then
                loop (n - 1) (this.ComposeAggregate(procName, data.Item(n - 1)) :: acc)
            else
                acc
        let definitions = loop data.Count []
        String.Join("\n\n", definitions)

    override this.GetExtendedTriggerDefinition(triggerName:string) : string =
        raise (NotImplementedException())
