á€áá¯á¶ááŒááºážááᯠáá±ážááẠááœááºážá¡á¬ážááŸá¬ áá±á¬ááºážáá«ážááŒá
áºáááºá
áá°áá®áá±á¬ á¡ááœá±ážá¡ááŒááºááŸá¬ á áááºáááºá á¬ážá áᬠáá±á¬ááºážááẠádatabase ááœáẠbusiness logic ááá¯á¡áá±á¬ááºá¡áááºáá±á¬áºáá«á".
áááááá¬áááá¬á áá«áá
áºáá±á¬ááºáááºážááá¯ááºáá°ážá
áá«á·á¡ááŒáẠá¡áá¬áááºá¡ááœááºá ááááá¯á¶ážá¡áá±áá²á· ááá¯áá·áºá¡ááœááºá á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡ááœááºáž áá±á«áºáá±á«ááºáá¬áá²á· á áááºáááºá á¬ážá áá¬áá±á¬ááºážáá²á· ááá¯ážáááºááŸá¯ááœá±ááᯠááááºážááááºážáá¬ážáá»ááºáá«áááºá á¡áá°ážáááŒáá·áº áááŒá¬áá±ážáá®á áááá¯áá¬ááá¬ááᯠááŒá±á¬ááºážáá²áááºááŸáá·áº á á®ážááœá¬ážáá±ážááá¯ááºáᬠáá¯áá¹áááá±áááᯠáá±á¬ááºáá¶á¡ááá·áºááá¯á· ááœáŸá²ááŒá±á¬ááºážááẠááá¬áá»á°áá¬áá»áá±á¬ áá¯á¶ážááŒááºáá»ááºáá áºáá¯ááᯠáááŒá¬áá±ážáá®á ááŒá¯áá¯ááºáá²á·áááºááᯠááá·áºááœááºážá ááºážá á¬ážáá«á ááá¯á·ááŸáᬠááœá¶á·ááŒáá¯ážááá¯ážáááºáá¬ááá»áŸ á¡áá¬á¡á¬ážáá¯á¶ážááẠáááºáá°áá áºáŠážáá áºáá±á¬ááºá¡ááœááºá០á¡áá¯á¶ážááááºáá±á¬á·áá² áááºáá°á·ááá¯áá»áŸ á áááºááááºá á¬ážáá±á¬á·áá«á
áá±á¬áºááŒáá¬ážáá±á¬ áááºážáááºážáá»á¬ážááẠááŸá¬ááœá±ááœá±á·ááŸáááŸá¯ ááá¯á·ááá¯áẠááŒáœááºážáá»ááºááá¯ááºáá±á áááºááá¯ááá¡áá¬á¡á¬ážáá¯á¶ážááẠááŸá±ážááá¯ážáááºááŒá®áž á¡ááŒáááºáá±á«ááºážáá»á¬ážá áœá¬ á¡áá±á¬ááºá¡áááºáá±á¬áºáá²á·ááẠ(á¥ááá¬á Oracle ááœáẠááœááºáá²á·áá±á¬ á¡ááŸá Ạ20 á á¡áá¬ážáá°áááºážáááºážááᯠá¡áá¯á¶ážááŒá¯áá²á·áááºá) á¡áá¬á¡á¬ážáá¯á¶ážááᯠáá áºáá±áá¬áááºážááœáẠá á¯áá±á¬ááºážááẠáá¯á¶ážááŒááºááá¯ááºáá«áááºá áá áºá á¯á¶áá áºáá±á¬ááºá¡ááœáẠá¡áá¯á¶ážáááºáá¬áá»áŸááºá áááºááœá±á·ááŒááá¬ážááá·áºá¡ááá¯ááºáž áá°áá®áá±á¬ á¡áá°á¡áááẠááá°áá®áá±á¬áá°áá»á¬ážáᶠááœááºáááºá áœá¬ áá±á¬ááºááŸááá¬áá±á·ááŸááááºá ááŒá®ážáá±á¬á· á¡á²áá«ááᯠá¡ááŸááºááá¡ááŒá Ạááá¯áá·áºá¡ááœáẠááááºážáá¬ážááᬠá¡áá¯á¶ážáááºáááºá
áá¯ááºáá«áááºá áá®ááá¹áá¬ááŸá¬ áááºá¡áá¬á០áááŒáá·áºá á¯á¶áá«áá°ážá á¡ááŸá¬ážááœá±áá²á· á¡ááŸá¬ážááœá±á áá¶ááá±á¬ááºážá¡ááŒá±á¬ááºážáááŸá áœá¬ ááŒá áºááá¯ááºáááºá áá±áááºááŸá¯áá»á¬ážááŸáá·áº ááŸááºáá»ááºáá»á¬ážááᯠááŒááºážááŒááºážáááºááẠááŒáá¯ááá¯ááŒá®áž áá»áŸá±á¬áºááá·áºáá«áááºá áá±á¬ááºáááºá¡áá±ážá áááºáá áºáᯠ- áááá»áá±á¬á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡áá±ážá áááºáá»á¬ážááᯠáá»ááºááŸááºáá¬ážáááºá ááá¯á·áá±á¬áºáááºáž á¡áá¬á¡á¬ážáá¯á¶ážááᯠáááá·áºáá¯ááºáááºážááœáẠááá±á¬áá»ááºááœáẠá¡áá¯á¶ážááŒá¯áá±áá²ááŒá áºáááºá ááá¯á·ááŒá±á¬áá·áº áá±á¬ááºážáá«ážááẠáá¯á¶ááŒááºážááŸáá·áº áá±áá°áá»ááá±á¬ááá¬ážá áá±á¬áºááŒáá»ááºáá»áŸáá¬ááŒá áºáááºá áá¬ááŸáááá¯áá«á áá¯á¶áá áºáá¯áá¯á¶ážááᯠáá¬ážáááºááẠáá¯á¶áá±á¬ááºáá±á¬á¡áá±ážá áááºá¡áá»ááºáá»á¬áž ááŸááááºáᯠáá»áŸá±á¬áºááá·áºáá«áááºá
áá±áá°áá» á¡áá°á¡áááŸá¬ "ááœá²áá±ááŒá®áž á¡á±á¬ááºááá¯ááºá áá¯ááºážá¡á±á¬ááºážááŒá®áž ááá¯ááºááá¯ááºáááº"
á¡áá¯ááºáá®áá¬ááẠááá¹ááááºááŒá
áºáá«ááẠ- ááá¬ážáá»á¬ážá¡ááœááºáá®ážááŒá¬ážá¡á
á®á¡á
ááºáá
áºáá¯á ááááºážáááºážáá¬ážáá±á¬áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá¡ááœááºáá®ážááŒá¬ážá¡á
á®á¡á
ááºáá
áºáá¯á
áá¯á¶ážá
áœá²áá°ááẠáá±áá¬ááᯠááá¯ááºááá¯ááºáááºáá±á¬ááºááœáá·áºáááŸááá«á Client áá¯ááºááá¯ááºááá»áŸ ááááºážáááºážáá¬ážáá±á¬ áá¯ááºáá±á¬ááºáá»ááºááᯠáá±á«áºááŒá®áž áááºáá¶áááŸáááá·áº áá¯á¶á·ááŒááºááŸá¯ááᯠáá¯ááºáá±á¬ááºáá«áááºá
á¡áááºážááá¹á
CREATE ROLE store;
CREATE ROLE sys_functions;
CREATE ROLE loc_audit_functions;
CREATE ROLE service_functions;
CREATE ROLE business_functions;
á¡á á®á¡á ááº
á á¬ážááœá²áááºááá¯ááŸá±á¬ááºááŸá¯á¡á á®á¡á ááº
áá¬áá¬áááºááá¯ááºáᬠá¡ááŒá±á¬ááºážá¡áá¬áá»á¬ážááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºáá±á¬ ááá¬ážáá»á¬ážááᯠáá áºááŸááºáá¬ážáá«á
CREATE SCHEMA store AUTHORIZATION store ;
á áá áºáá¯ááºáá±á¬ááºáá»áẠááá¬áž
á¡áá°ážáááŒáá·áº ááŸááºáááºážááá¬ážááŒá±á¬ááºážáá²ááŸá¯áá»á¬ážá¡ááœáẠá áá áºáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá
CREATE SCHEMA sys_functions AUTHORIZATION sys_functions ;
ááŒááºááœááºážá á¬áááºážá á áºá¡á á®á¡á ááº
ááááºážáááºážáá¬ážáá±á¬ áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááŸáá·áº áá áºááŸááºááá¬ážáá»á¬ážááá¯á· ááŒá±á¬ááºážáá²ááŸá¯áá»á¬ážááᯠáá±áááá¯ááºáᬠá á¬áááºážá á áºáá»á¬áž á¡áá±á¬ááºá¡áááºáá±á¬áºááẠáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááŸáá·áº ááá¬ážáá»á¬ážá
CREATE SCHEMA loc_audit_functions AUTHORIZATION loc_audit_functions;
áááºáá±á¬ááºááŸá¯ áá¯ááºáá±á¬ááºáá»áẠáá¯á¶ááŒááºáž
áááºáá±á¬ááºááŸá¯ááŸáá·áº DML áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá¡ááœáẠáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá
CREATE SCHEMA service_functions AUTHORIZATION service_functions;
áá¯ááºáááºážáá±á¬ááºááœááºáá»áẠááá¬áž
client ááŸáá±á«áºáá±á¬ áá±á¬ááºáá¯á¶ážáá¯ááºáááºážáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá¡ááœáẠáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá
CREATE SCHEMA business_functions AUTHORIZATION business_functions;
á¡áá¯á¶ážááŒá¯ááœáá·áºá¡ááœáá·áºá¡áá±áž
á¡áááºážááá¹á - DBA á¡á á®á¡á ááºá¡á¬ážáá¯á¶ážááᯠ(DB Owner á¡áááºážááá¹áá០ááœá²áá¯ááºáá¬ážáááº)á
CREATE ROLE dba_role;
GRANT store TO dba_role;
GRANT sys_functions TO dba_role;
GRANT loc_audit_functions TO dba_role;
GRANT service_functions TO dba_role;
GRANT business_functions TO dba_role;
á¡áááºážááá¹á - á¡áá¯á¶ážááŒá¯áá°ááᯠá¡ááœáá·áºáá°ážááŸááááºá áááºáá Ạdiagram áá²ááŸá¬ áá¯ááºáááºáž_áá¯ááºáá±á¬ááºáá»ááºáá»á¬áž.
CREATE ROLE user_role;
á¡á á®á¡á á¥áºáá»á¬ážá¡ááŒá¬áž á¡ááœáá·áºáá°ážáá»á¬áž
Grant
áá¯ááºáá±á¬ááºáá»ááºá¡á¬ážáá¯á¶ážááᯠattribute ááŒáá·áº áááºáá®ážáá¬ážáá±á¬ááŒá±á¬áá·áºááŒá
áºáááºá áá¯á¶ááŒá¯á¶áá±ážáááºááŸááºáá° ááá¯á¡ááºáá±á¬ááœáŸááºááŒá¬ážáá»ááºáá»á¬áž áá¯ááºáá±á¬ááºáá»ááºá¡á¬ážáá¯á¶ážááᯠááŒááºáááºáá¯ááºááááºážáá«... á¡áá»á¬ážáá°ááŸá¬áá¶ááŸá
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA sys_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA loc_audit_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA service_functions FROM public ;
REVOKE EXECUTE ON ALL FUNCTION IN SCHEMA business_functions FROM public ;
GRANT USAGE ON SCHEMA sys_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA sys_functions TO dba_role ;
GRANT USAGE ON SCHEMA loc_audit_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA loc_audit_functions TO dba_role ;
GRANT USAGE ON SCHEMA service_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA service_functions TO dba_role ;
GRANT USAGE ON SCHEMA business_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO dba_role ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA business_functions TO user_role ;
GRANT ALL PRIVILEGES ON SCHEMA store TO GROUP business_functions ;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA store TO business_functions ;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA store TO business_functions ;
áá«ááᯠdatabase schema á¡áááºááá·áºááŒá áºáá«ááŒá®á áá±áá¬ááᯠá áááºááŒáá·áºááœááºážááá¯ááºáá«áááºá
áá áºááŸááºááá¬ážáá»á¬áž
ááá¬ážáá»á¬ážáááºáá®ážááŒááºážááẠá¡áá±ážá¡ááœá²ááŒá áºáááºá á¡áá¯á¶ážáááŒá¯ááẠáá¯á¶ážááŒááºáá²á·áááºááŸááœá²á á¡áá°ážá¡ááºá¹áá«áááºáá»á¬áž áááŸááá«á Serial ááŒá®ážááŒááºáá¬ážá áœá¬ sequences ááá¯áááºáá®ážáá«á ááá¯á·á¡ááŒááºá áá¯ááºáá«áááºá ááœáŸááºááŒá¬ážáá»ááºáá»á¬ážááá¯á¡áá»á¬ážáá¯á¶ážá¡áá¯á¶ážááŒá¯áá«á
COMMENT ON ...
ááŸááºáá»ááºáá»á¬áž вÑÐµÑ ááŒáœááºážáá»ááºáááŸá á¡áá¬ááá¹áá¯áá»á¬ážá
ááŒááºááœááºážá á¬áááºážá á áº
ááááºážáááºážáá¬ážáá±á¬ áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááŸáá·áº áá áºááŸááºááá¬ážáá»á¬ážáá®ááá¯á· ááŒá±á¬ááºážáá²ááŸá¯áá»á¬ážááᯠááŸááºáááºážáááºáááºá¡ááœááºá á¡ááŒá¬ážá¡áá¬áá»á¬ážáá²ááœááºá áá±á¬ááºáááºáá»áááºáááºááŸá¯áá¡áá±ážá áááºá¡áá»ááºá¡áááºáá»á¬ážá áá¯áá±á«áºáá±á¬ áá±á¬áºáá»á°ážááá¶ááááºá ááŸáá·áº ááá·áºááœááºážááŸá¯á á¡ááŸááºááááºáááºááá¯ážáá»á¬áž á¡áá«á¡áááºá JSON á áá¯á¶á á¶ááŒáá·áº á¡ááœááºáá±á¬ááºáá»á¬ážá
á áá áºáá¯ááºáá±á¬ááºáá»ááºáá»á¬áž
áá áºááŸááºááá¬ážáá»á¬ážááœáẠá¡ááŒá±á¬ááºážá¡áá²áá»á¬ážááᯠááŸááºáááºážáááºáááºá¡ááœáẠáá®ááá¯ááºážáá¯ááºáá¬ážáááºá áááºážááá¯á·ááẠá¡á áá»áá¯ážáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááŒá áºáááºá
ááá°áá¬áá¯á¶á ᶠ- á áá áºáá¯ááºáá±á¬ááºáá»ááº
---------------------------------------------------------
-- INSERT
CREATE OR REPLACE FUNCTION sys_functions.table_insert_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'insert' , json_build_object('id', NEW.id) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_insert AFTER INSERT ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_insert_log();
---------------------------------------------------------
-- UPDATE
CREATE OR REPLACE FUNCTION sys_functions.table_update_log ()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.column != NEW.column
THEN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'update' , json_build_object('OLD.column', OLD.column , 'NEW.column' , NEW.column ) );
END IF ;
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_update AFTER UPDATE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_update_log ();
---------------------------------------------------------
-- DELETE
CREATE OR REPLACE FUNCTION sys_functions.table_delete_log ()
RETURNS TRIGGER AS $$
BEGIN
PERFORM loc_audit_functions.make_log( ' '||'table' , 'delete' , json_build_object('id', OLD.id ) );
RETURN NULL ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER table_after_delete AFTER DELETE ON storage.table FOR EACH ROW EXECUTE PROCEDURE sys_functions.table_delete_log ();
áááºáá±á¬ááºááŸá¯áá¯ááºáááºážáá±á¬ááºáá¬áá»á¬áž
áá áºááŸááºááá¬ážáá»á¬ážááœáẠáááºáá±á¬ááºááŸá¯ááŸáá·áº DML áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááẠáá®ááá¯ááºážáá¯ááºáá¬ážáááºá
ááá°áá¬áá¯á¶á ᶠ- áááºáá±á¬ááºááŸá¯áá¯ááºáá±á¬ááºáá»ááº
--INSERT
--RETURN id OF NEW ROW
CREATE OR REPLACE FUNCTION service_functions.table_insert ( new_column store.table.column%TYPE )
RETURNS integer AS $$
DECLARE
new_id integer ;
BEGIN
-- Generate new id
new_id = nextval('store.table.seq');
-- Insert into table
INSERT INTO store.table
(
id ,
column
)
VALUES
(
new_id ,
new_column
);
RETURN new_id ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
--DELETE
--RETURN ROW NUMBERS DELETED
CREATE OR REPLACE FUNCTION service_functions.table_delete ( current_id integer )
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
DELETE FROM store.table WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- UPDATE DETAILS
-- RETURN ROW NUMBERS UPDATED
CREATE OR REPLACE FUNCTION service_functions.table_update_column
(
current_id integer
,new_column store.table.column%TYPE
)
RETURNS integer AS $$
DECLARE
rows_count integer ;
BEGIN
UPDATE store.table
SET
column = new_column
WHERE id = current_id;
GET DIAGNOSTICS rows_count = ROW_COUNT;
RETURN rows_count ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
áá¯ááºáááºážáá±á¬ááºáá¬áá»á¬áž
client ááŸáá±á«áºáá±á¬áá±á¬ááºáá¯á¶ážá á®ážááœá¬ážáá±ážáá¯ááºáááºážáá±á¬ááºáá¬áá»á¬ážá¡ááœááºáá®ááá¯ááºážááŒá¯áá¯ááºáá¬ážáááºá á¡ááŒá²áááºážááŒááºáá¬ááŒáááº- JSON. áá¯ááºáá±á¬ááºáá»ááºá¡ááŸá¬ážáá»á¬ážááᯠááŒá¬ážááŒááºááŒá®áž ááŸááºáááºážááá°áááºá ááá±á¬ááºááᯠá¡áá¯á¶ážááŒá¯áá«á ááŒáœááºážáá»ááº.
ááá°áá¬áá¯á¶á ᶠ- áá¯ááºáááºážáá¯ááºáá±á¬ááºááŸá¯
CREATE OR REPLACE FUNCTION business_functions.business_function_template(
--Input parameters
)
RETURNS JSON AS $$
DECLARE
------------------------
--for exception catching
error_message text ;
error_json json ;
result json ;
------------------------
BEGIN
--LOGGING
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'STARTED',
json_build_object
(
--IN Parameters
)
);
PERFORM business_functions.notice('business_function_template');
--START BUSINESS PART
--END BUSINESS PART
-- SUCCESFULLY RESULT
PERFORM business_functions.notice('result');
PERFORM business_functions.notice(result);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'FINISHED',
json_build_object( 'result',result )
);
RETURN result ;
----------------------------------------------------------------------------------------------------------
-- EXCEPTION CATCHING
EXCEPTION
WHEN OTHERS THEN
PERFORM loc_audit_functions.make_log
(
'business_function_template',
'STARTED',
json_build_object
(
--IN Parameters
) , TRUE );
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLSTATE',SQLSTATE ), TRUE
);
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR',
json_build_object('SQLERRM',SQLERRM ), TRUE
);
GET STACKED DIAGNOSTICS error_message = RETURNED_SQLSTATE ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-RETURNED_SQLSTATE',json_build_object('RETURNED_SQLSTATE',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = COLUMN_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-COLUMN_NAME',
json_build_object('COLUMN_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = CONSTRAINT_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-CONSTRAINT_NAME',
json_build_object('CONSTRAINT_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_DATATYPE_NAME ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_DATATYPE_NAME',
json_build_object('PG_DATATYPE_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = MESSAGE_TEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-MESSAGE_TEXT',json_build_object('MESSAGE_TEXT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = SCHEMA_NAME ;
PERFORM loc_audit_functions.make_log
(s
'business_function_template',
' ERROR-SCHEMA_NAME',json_build_object('SCHEMA_NAME',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_DETAIL ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_DETAIL',
json_build_object('PG_EXCEPTION_DETAIL',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_HINT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_HINT',json_build_object('PG_EXCEPTION_HINT',error_message ), TRUE );
GET STACKED DIAGNOSTICS error_message = PG_EXCEPTION_CONTEXT ;
PERFORM loc_audit_functions.make_log
(
'business_function_template',
' ERROR-PG_EXCEPTION_CONTEXT',json_build_object('PG_EXCEPTION_CONTEXT',error_message ), TRUE );
RAISE WARNING 'ALARM: %' , SQLERRM ;
SELECT json_build_object
(
'isError' , TRUE ,
'errorMsg' , SQLERRM
) INTO error_json ;
RETURN error_json ;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;
ááááº
áá±áá°áá»áá¯á¶ááœá±ááᯠáá±á¬áºááŒááá¯á· áá¯á¶áá±á¬ááºááŒá®áááºáá«áááºá áááºáá°áááá¯á¡áá±ážá áááºááŸáá·áºááááºáá»á¬ážááá¯á áááºáá«áááºá á¬ážáá«áááŸááºáá»ááºáá»á¬ážáá±ážáá«á áá¯á¶ááœááºáááºáá¶ááááœá±á·ááŸá¯áá»á¬ážáááºááá·áºáááºáá»á±á¬áºááœáŸááºáááá·áºáááºá
PS
ááá¯ážááŸááºážáá±á¬ á¡ááŸá¬ážáá áºáá¯ááᯠááŸááºáááºážáááºááŒááºáž - ááá·áºááœááºážááŸá¯ ááá·áºáááºáá»áẠá¡áá»áá¯ážá¡á á¬áž
-[ RECORD 1 ]- date_trunc | 2020-08-19 13:15:46 id | 1072 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | STARTED jsonb_pretty | { | "dko": { | "id": 4, | "type": "Type1", | "title": "CREATED BY addKD", | "Weight": 10, | "Tr": "300", | "reduction": 10, | "isTrud": "TRUE", | "description": "decription", | "lowerTr": "100", | "measurement": "measurement1", | "methodology": "m1", | "passportUrl": "files", | "upperTr": "200", | "weightingFactor": 100.123, | "actualTrValue": null, | "upperTrCalcNumber": "120" | }, | "CardId": 3 | } -[ RECORD 2 ]- date_trunc | 2020-08-19 13:15:46 id | 1073 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR jsonb_pretty | { | "SQLSTATE": "22P02" | } -[ RECORD 3 ]- date_trunc | 2020-08-19 13:15:46 id | 1074 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR jsonb_pretty | { | "SQLERRM": "invalid input syntax for type numeric: "null"" | } -[ RECORD 4 ]- date_trunc | 2020-08-19 13:15:46 id | 1075 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-RETURNED_SQLSTATE jsonb_pretty | { | "RETURNED_SQLSTATE": "22P02" | } -[ RECORD 5 ]- date_trunc | 2020-08-19 13:15:46 id | 1076 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-COLUMN_NAME jsonb_pretty | { | "COLUMN_NAME": "" | } -[ RECORD 6 ]- date_trunc | 2020-08-19 13:15:46 id | 1077 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-CONSTRAINT_NAME jsonb_pretty | { | "CONSTRAINT_NAME": "" | } -[ RECORD 7 ]- date_trunc | 2020-08-19 13:15:46 id | 1078 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_DATATYPE_NAME jsonb_pretty | { | "PG_DATATYPE_NAME": "" | } -[ RECORD 8 ]- date_trunc | 2020-08-19 13:15:46 id | 1079 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-MESSAGE_TEXT jsonb_pretty | { | "MESSAGE_TEXT": "invalid input syntax for type numeric: "null"" | } -[ RECORD 9 ]- date_trunc | 2020-08-19 13:15:46 id | 1080 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-SCHEMA_NAME jsonb_pretty | { | "SCHEMA_NAME": "" | } -[ RECORD 10 ]- date_trunc | 2020-08-19 13:15:46 id | 1081 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_EXCEPTION_DETAIL jsonb_pretty | { | "PG_EXCEPTION_DETAIL": "" | } -[ RECORD 11 ]- date_trunc | 2020-08-19 13:15:46 id | 1082 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_EXCEPTION_HINT jsonb_pretty | { | "PG_EXCEPTION_HINT": "" | } -[ RECORD 12 ]- date_trunc | 2020-08-19 13:15:46 id | 1083 usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-PG_EXCEPTION_CONTEXT jsonb_pretty | { usename | emp1 log_module | addKD log_module_hash | 0b4c1529a89af3ddf6af3821dc790e8a status | ERROR-MESSAGE_TEXT jsonb_pretty | { | "MESSAGE_TEXT": "invalid input syntax for type numeric: "null"" | }
source: www.habr.com