Skip to content

SQL - PostgreSQL - ETL Function Template

Source: A “best practice” Postgresql function template for ETL processes - Database Administrators Stack Exchange

create or replace function 
    table_schema.load_table_name(l_job_date date) 
returns
    void as $$
declare
    -- control variables
    l_job_code              constant varchar(50) = 'table_schema.load_table_name';
    l_job_start             timestamp;
    l_job_end               timestamp;

    -- exception management variables       
    l_exception_error_code  text;
    l_exception_message     text;
    l_exception_detail      text;
    l_exception_hint        text;
    l_exception_context     text;
begin
    -- capture start
    l_job_start = clock_timestamp();

    -- do stuff here
    -- ...
    -- ...
    -- ...

    -- capture end
    l_job_end =  = clock_timestamp();

    -- log success
    perform fn_log_job(
        l_job_code
        ,l_job_date
        ,l_job_start
        ,l_job_end
        ,'success'
    )
    ;

-- if something "breaks" do the following
exception when others then
    get stacked diagnostics 
        l_exception_error_code  = RETURNED_SQLSTATE
        ,l_exception_message        = MESSAGE_TEXT
        ,l_exception_detail     = PG_EXCEPTION_DETAIL
        ,l_exception_hint           = PG_EXCEPTION_HINT
        ,l_exception_context        = PG_EXCEPTION_CONTEXT
    ;

    -- log exception for debugging
    perform fn_log_exception(
        l_exception_error_code
        ,l_exception_message
        ,l_exception_detail
        ,l_exception_hint
        ,l_exception_context
    )
    ;

    -- log job failure
    perform fn_log_job(
        l_job_code
        ,l_job_date
        ,l_job_start
        ,l_job_end
        ,'fail'
    )
    ;

end;
$$ language plpgsql;

See Also:

Backlinks:

list from [[SQL - PostgreSQL ETL Function Template]] AND -"Changelog"