-- FILE Name: add_expand_fields.sql
-- 功能描述 : 给无扩展字段的表增加扩展字段

CREATE OR REPLACE
FUNCTION add_expand_field_operate( IN schemaName TEXT, IN tableName TEXT, IN delFlag BOOLEAN ) RETURNS varchar AS $BODY$
DECLARE
--加列
add_table_columns VARCHAR;
--扩展字段1
expand_source VARCHAR := 'source';
--扩展字段2
expand_op_type VARCHAR := 'op_type';
--扩展字段3
expand_op_ts VARCHAR := 'op_ts';
--扩展字段4
expand_current_ts VARCHAR := 'current_ts';
--扩展字段5
expand_op_position VARCHAR := 'op_position';
--扩展字段6
expand_pos VARCHAR := 'pos';
--扩展字段7 删除标记
expand_del VARCHAR := 'delflag';
--扩展字段前缀
expand_prefix VARCHAR := 'ex_';
--扩展字段总个数
expand_num INTEGER := 7;
--扩展字段遍历变量
counter INTEGER := 0 ;
--当前扩展字段在表中个数,若有存在则为1，不存在则为0，存在时加扩展字段前缀
expand_count INTEGER := -1;
--扩展字段名称变量
expand_field VARCHAR;
--扩展字段类型变量
expand_field_type VARCHAR;
--返回结果
P_OUT_ERRMSG VARCHAR(5000):= 'ADD Expand Field SUCCEED!';
BEGIN
    RAISE NOTICE 'tableName: %',
    tableName;

    WHILE counter < expand_num
        LOOP
            counter := counter + 1 ;
            IF
                ( counter = 1 ) THEN expand_field := expand_source;
                expand_field_type := 'text';
            ELSIF ( counter = 2) THEN expand_field := expand_op_type;
                expand_field_type := 'text';
            ELSIF ( counter = 3) THEN expand_field := expand_op_ts;
                expand_field_type := 'text';
            ELSIF ( counter = 4) THEN expand_field := expand_current_ts;
                expand_field_type := 'text';
            ELSIF ( counter = 5) THEN expand_field := expand_op_position;
                expand_field_type := 'text';
            ELSIF ( counter = 6) THEN expand_field := expand_pos;
                expand_field_type := 'bigint';
            ELSIF ( counter = 7) THEN
                IF
                    (delFlag = FALSE) THEN expand_field := NULL;
                    expand_field_type := NULL;
                ELSE expand_field := expand_del;
                    expand_field_type := 'INTEGER';
                END
                IF;
                ELSE RAISE EXCEPTION 'Invalid counter %',
                    counter;
                END
                IF;

                    RAISE NOTICE 'expand_field: %',
                    expand_field;

                    RAISE NOTICE 'expand_field_type: %',
                    expand_field_type;

                    SELECT
                    count(*)
                    INTO
                    expand_count
                    FROM
                    information_schema.columns
                    WHERE
                    table_schema = schemaName
                    AND table_name = tableName
                    AND column_name = expand_field;

                    RAISE NOTICE 'expand_count: %',
                    expand_count;

                    WHILE (expand_count != 0) LOOP expand_field := expand_prefix || expand_field;

                        RAISE NOTICE 'expand_field: %',
                        expand_field;

                        SELECT
                        count(*)
                        INTO
                        expand_count
                        FROM
                        information_schema.columns
                        WHERE
                        table_schema = schemaName
                        AND table_name = tableName
                        AND column_name = expand_field;

                        RAISE NOTICE 'expand_count: %',
                        expand_count;
                END LOOP;
                --add_table_columns := 'ALTER TABLE '||tableName||' DROP COLUMN '||expand_field||';';
                add_table_columns := 'ALTER TABLE "' || schemaName || '"."' || tableName || '" ADD COLUMN ' || expand_field || ' ' || expand_field_type || ';';

                RAISE NOTICE 'add_table_columns: %',
                add_table_columns;

                IF (expand_field IS NOT NULL) THEN EXECUTE IMMEDIATE add_table_columns;
                END IF;
            END LOOP;

            RETURN P_OUT_ERRMSG;
        END;

        $BODY$ LANGUAGE PLPGSQL VOLATILE;



CREATE OR REPLACE
FUNCTION add_expand_field( IN schemaName TEXT, IN tableName TEXT, IN delFlag BOOLEAN ) RETURNS varchar AS $BODY$
DECLARE
--查询表名
query_table VARCHAR;
--查询表是否存在
query_table_exist VARCHAR;
--该schema下表个数
table_num BIGINT;
--该表个数，若存在则为1，不存在则为0
table_count BIGINT;
--定义游标类型
TYPE app_ref_cur_type IS REF CURSOR;
--定义游标变量
my_cur app_ref_cur_type;

table_name TEXT;
--返回结果
P_OUT_ERRMSG VARCHAR(5000):= 'ADD Expand Field SUCCEED!';
BEGIN
    BEGIN
        RAISE NOTICE 'schemaName: %',
        schemaName;

        SELECT
        count(*)
        INTO
        table_num
        FROM
        information_schema.tables
        WHERE
        table_schema = schemaName;
        --当schema不存在或者未输入schema参数时会报此错
        IF (table_num <= 0) THEN RAISE EXCEPTION 'Invalid schemaName: %',
            schemaName;
        END IF;

        RAISE NOTICE 'tableName: %',
        tableName;

        IF(tableName IS NULL) THEN query_table := 'SELECT tablename FROM pg_tables where schemaname =''' || schemaName || '''';

            RAISE NOTICE 'query_table: %',
            query_table;

            OPEN my_cur FOR query_table;

            FETCH my_cur
            INTO
            table_name;

            WHILE my_cur%FOUND LOOP RAISE NOTICE '22 table_name: %',
                table_name;

                add_expand_field_operate(schemaName,
                    table_name,
                    delFlag);

                FETCH my_cur
                INTO
                table_name;
        END LOOP;
    ELSE query_table_exist := 'SELECT count(*) FROM information_schema.tables WHERE
        table_schema=''' || schemaName || ''' AND table_name=''' || tableName || ''';';

        RAISE NOTICE 'query_table_exist: %',
        query_table_exist;

        EXECUTE query_table_exist
        INTO
        table_count ;

        RAISE NOTICE 'table_count: %',
        table_count;

        IF (table_count <= 0) THEN RAISE EXCEPTION 'Invalid tableName: %',
            tableName;
        END IF;

        add_expand_field_operate(schemaName,
            tableName,
            delFlag);
    END IF;

    RETURN P_OUT_ERRMSG;

    EXCEPTION
    WHEN OTHERS THEN P_OUT_ERRMSG := 'ADD Expand Field ERROR!';

        ROLLBACK;

        RETURN P_OUT_ERRMSG;
END;
END;

$BODY$ LANGUAGE PLPGSQL VOLATILE;
