Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create or replace function EXECUTE_WO_RETURN_LOGGED(
- I_STATEMENT_TO_EXECUTE in text,
- I_LEVEL in text default 'LOG',
- I_REPORT_ERRORS_ONLY in boolean default false
- )
- returns bigint
- language plpgsql
- security invoker
- volatile
- set search_path = --current user,
- :SCHEMA_NAME_LOGGER,
- :SCHEMA_NAME,
- :SCHEMA_NAME_UTILS,
- public
- as
- $body$
- declare
- -- Constants
- C_LB constant varchar(2) default e'\n';
- -- Variables
- V_TEXT text;
- V_REC record;
- V_ROW_COUNT bigint
- default 0;
- V_EXCEPTION_OCCURRED boolean
- default false;
- V_SQLSTATE char(5);
- V_STACK text
- default '!!!no data found!!!';
- V_DETAILS text
- default '!!!no data found!!!';
- V_HINT text
- default '!!!no data found!!!';
- begin
- execute I_STATEMENT_TO_EXECUTE;
- get current diagnostics V_ROW_COUNT = ROW_COUNT;
- V_TEXT :=
- 'Following statement has been executed successfully affecting ';
- case V_ROW_COUNT
- when 0 then
- V_TEXT := V_TEXT || ' no row.';
- when 1 then
- V_TEXT := V_TEXT || ROW_COUNT || ' row.';
- else
- V_TEXT := V_TEXT || ROW_COUNT || ' rows.';
- end case;
- V_TEXT := V_TEXT || C_LB || C_LB || '-- Statement' || C_LB ||
- I_STATEMENT_TO_EXECUTE;
- case I_LEVEL
- when 'DEBUG' then
- perform DEBUG(I_MESSAGE => V_TEXT) ;
- when 'LOG' then
- perform LOG(I_MESSAGE => V_TEXT) ;
- when 'INFO' then
- perform INFO(I_MESSAGE => V_TEXT) ;
- when 'NOTICE' then
- perform NOTICE(I_MESSAGE => V_TEXT) ;
- when 'WARNING' then
- perform WARNING(I_MESSAGE => V_TEXT) ;
- when 'EXCEPTION' then -- maybe pointless but there you go ;-)
- perform EXCEPTION(I_MESSAGE => V_TEXT) ;
- else
- raise exception '%', 'Unsupported level ' || I_LEVEL;
- end case;
- return V_ROW_COUNT;
- exception
- when others then
- if I_REPORT_ERRORS_ONLY then
- get stacked diagnostics V_DETAILS = PG_EXCEPTION_DETAIL;
- get stacked diagnostics V_HINT = PG_EXCEPTION_HINT;
- get stacked diagnostics V_STACK = PG_EXCEPTION_CONTEXT;
- perform WARNING(
- I_MESSAGE => GET_FORMATTED_EXCEPTION_MESSAGE(
- I_STATEMENT_WITH_PARAMETERS =>
- I_STATEMENT_TO_EXECUTE,
- I_SQLSTATE =>
- SQLSTATE,
- I_SQLERRM =>
- SQLERRM,
- I_DETAILS =>
- V_DETAILS,
- I_HINT =>
- V_HINT,
- I_STACK =>
- V_STACK
- )
- );
- return null;
- else
- raise;
- end if;
- end;
- $body$;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement