CREATE OR REPLACE FUNCTION dbo.pnewdataimportid(
    par_datasourcetype character varying,
    par_datasource character varying,
    par_datadate timestamp without time zone,
    par_description character varying DEFAULT NULL::character varying,
    par_executionid character varying DEFAULT NULL::character varying,
    par_value1 character varying DEFAULT NULL::character varying,
    par_value2 character varying DEFAULT NULL::character varying)
    RETURNS TABLE(datacontractid bigint, dataimportid bigint, createtime timestamp without time zone, starttime timestamp without time zone, value1 character varying, value2 character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_DataSourceTypeID INTEGER;
    var_CreateTime TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP(3);
    var_DataContractID BIGINT;
    var_DataImportID BIGINT;
    SCOPE_IDENTITY BIGINT;
BEGIN
RAISE NOTICE 'par_datadate: %', par_datadate;  
RAISE NOTICE 'par_datasourcetype: %', par_datasourcetype;  

    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    SELECT
        dst.datasourcetypeid, COALESCE(par_Value1, dst.value1)
        INTO var_DataSourceTypeID, par_Value1
        /* , */
        /* @Value2              = ISNULL(@Value2, DST.Value2) */
        FROM dbo.datasourcetype_staging AS dst
        WHERE LOWER(dst.description) = LOWER(par_DataSourceType);

RAISE NOTICE 'var_DataSourceTypeID: %', var_DataSourceTypeID;  
RAISE NOTICE 'par_Value1: %', par_Value1;  

    IF var_DataSourceTypeID IS NULL THEN
        --RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 16, 0, 'Unknown DataSourceType ''%s''', par_DataSourceType USING ERRCODE = '50000';
        -- return_code := 1;
        RETURN;
    END IF;

    IF par_Description IS NULL THEN
        par_Description := LEFT(par_DataSource, 255);
    END IF;

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN;
            */
            BEGIN
            END;
        END IF;

RAISE NOTICE 'par_Description: %', par_Description; 
RAISE NOTICE 'var_DataSourceTypeID: %', var_DataSourceTypeID; 
RAISE NOTICE 'par_DataSource: %', par_DataSource; 
RAISE NOTICE 'var_CreateTime: %', var_CreateTime; 
RAISE NOTICE 'par_DataDate: %', par_DataDate; 
RAISE NOTICE 'par_Value1: %', par_Value1; 
RAISE NOTICE 'par_Value2: %', par_Value2; 

RAISE NOTICE 'par_Description: %', par_Description; 
RAISE NOTICE 'var_DataSourceTypeID: %', var_DataSourceTypeID; 
RAISE NOTICE 'par_DataSource: %', par_DataSource; 

         INSERT INTO dbo.datacontract_staging (description, datasourcetypeid, datasource, createtime, datadate, value1, value2)
         VALUES (par_Description, var_DataSourceTypeID, par_DataSource, var_CreateTime, par_DataDate, par_Value1, par_Value2);
         -- RETURNING dbo.datacontract_staging.datacontractid into var_DataContractID;

select dci.datacontractid 
into var_DataContractID
from dbo.datacontractid_staging dci
where dci.description = par_Description 
    and dci.datasourcetypeid = var_DataSourceTypeID 
    and dci.datasource = par_DataSource
order by dci.datacontractid desc 
limit 1;

        --INSERT INTO dbo.datacontract_staging (description, datasourcetypeid, datasource, createtime, datadate, value1, value2)
        --VALUES (par_Description, var_DataSourceTypeID, par_DataSource, var_CreateTime, TO_TIMESTAMP(par_DataDate, 'YYYY-MM-DD HH:MI:SS'), par_Value1, par_Value2)
        --RETURNING dbo.datacontract_staging.datacontractid into var_DataContractID;
        
        -- var_DataContractID := SCOPE_IDENTITY;
RAISE NOTICE 'INSERT INTO dbo.datacontract_staging: var_DataContractID: %', var_DataContractID; 

        INSERT INTO dbo.dataimport_staging (datacontractid, dataimportstatus, createtime, starttime, completetime, message, executionid)
        VALUES (var_DataContractID, 'Started', var_CreateTime, var_CreateTime, NULL, NULL, rtrim(ltrim(par_ExecutionID,'{'),'}') );
        -- RETURNING dbo.dataimport.dataimportid into var_DataImportID;

select dci.dataimportid 
into var_DataImportID
from dbo.dataimportid_staging dci
where dci.datacontractid = var_DataContractID 
    and dci.dataimportstatus = 'Started' 
order by dci.dataimportid desc 
limit 1;

RAISE NOTICE 'INSERT INTO dbo.dataimport: var_DataImportID: %', var_DataImportID; 
        --var_DataImportID := SCOPE_IDENTITY;

        RETURN QUERY
        SELECT
            var_DataContractID AS datacontractid, var_DataImportID AS dataimportid, var_CreateTime AS createtime, var_CreateTime AS starttime,
            par_Value1 AS value1, par_Value2 AS value2;

        IF var_TranCountOnEntry = 0 THEN
            COMMIT;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                IF var_TranCountOnEntry = 0 THEN
                    ROLLBACK;
                END IF;
                RAISE;
    END;
    -- return_code := 0;
    RETURN;
RAISE NOTICE 'function clickdw.dbo.pnewdataimportid';                                          
END;
$BODY$;

ALTER FUNCTION dbo.pnewdataimportid(character varying, character varying, timestamp without time zone, character varying, character varying, character varying, character varying)
    OWNER TO postgres;
