functions/WRITE_MESSAGE_TO_TABLE.pg_sql

Functions

Source

0001: \echo Start functions/WRITE_MESSAGE_TO_TABLE.pg_sql
0002: 
0003: /** Write given parameters into the logging table.
0004:  *  It is meant to be called in an autonomous transaction to and best done
0005:  *  in a separate process to reduce impact on main transaction as much as
0006:  *  possible.
0007:  *
0008:  *  @function WRITE_MESSAGE_TO_TABLE
0009:  *  @return void
0010:  *  @param in text I_FUNCTION Value for LOG.FUNCTION.
0011:  *  @param in text I_MESSAGE Value for LOG.MESSAGE.
0012:  *  @param in text I_LEVEL Value for LOG.LEVEL.
0013:  *  @param in name I_PRESENT_USER Value for LOG.PRESENT_USER.
0014:  *  @param in name I_SESSION_USER Value for LOG.SESS_USER.
0015:  *  @param in timestamp_with_timezone I_TRANSACTION_TIMESTAMP Value for LOG.TRANSACTION_TIMESTAMP.
0016:  *  @param in bigint I_TRANSACTION_ID Value for LOG.TRANSACTION_ID.
0017:  *  @param in bigint I_SERVER_PID Value for LOG.SERVER_PID.
0018:  *  @param in inet I_REMOTE_ADDRESS Value for LOG.REMOTE_ADDRESS.
0019:  *  @param in int I_REMOTE_PORT Value for LOG.REMOTE_PORT.
0020:  *  @private
0021:  *  @info This function is meant to be private. Its use outside pglogger is
0022:  *        very limited at most. That's why it is marked as private eventhough
0023:  *        it is visible to the outside. All of this is due to the fact that
0024:  *        PostgreSQL 10 and earlier does not support packages.
0025:  *  @version $Id: WRITE_MESSAGE_TO_TABLE.pg_sql 19 2018-06-15 20:24:38Z thiemo $
0026:  *  @todo Once PostgreSQL supports packages, integrate this function into one.
0027:  *        For the time being, it should get installed within a schema on its
0028:  *        own and no execution grants should be given on it.
0029:  *  @todo Once HyperSQL supports a license tag, convert the corresponding
0030:  *        info tag into one.
0031:  *  @copyright Thiemo Kellner, 2018 -
0032:  *  @info License LGPLv3
0033:  *  @webpage https://www.sourceforge.net/projects/pglogger
0034:  */
0035: create or replace function WRITE_MESSAGE_TO_TABLE(
0036:     I_FUNCTION text,
0037:     I_MESSAGE text,
0038:     I_LEVEL text,
0039:     I_PRESENT_USER name,
0040:     I_SESSION_USER name,
0041:     I_TRANSACTION_TIMESTAMP timestamp with time zone,
0042:     I_TRANSACTION_ID bigint,
0043:     I_SERVER_PID bigint,
0044:     I_REMOTE_ADDRESS inet,
0045:     I_REMOTE_PORT int
0046: )
0047:   returns void
0048:   language plpgsql
0049:   volatile
0050:   -- Include the hosting schema into search_path so that dblink
0051:   -- can find the pglogger objects. There is no need to access
0052:   -- objects in other schematas not covered with public.
0053:   set search_path = :SCHEMA_NAME, public
0054:   as
0055: $body$
0056:     begin
0057:         insert into LOG(
0058:             FUNCTION,
0059:             MESSAGE,
0060:             LEVEL,
0061:             PRESENT_USER,
0062:             SESS_USER,
0063:             TRANSACTION_TIMESTAMP,
0064:             TRANSACTION_ID,
0065:             SERVER_PID,
0066:             REMOTE_ADDRESS,
0067:             REMOTE_PORT
0068:         ) values (
0069:             I_FUNCTION,
0070:             I_MESSAGE,
0071:             I_LEVEL,
0072:             I_PRESENT_USER,
0073:             I_SESSION_USER,
0074:             I_TRANSACTION_TIMESTAMP,
0075:             I_TRANSACTION_ID,
0076:             I_SERVER_PID,
0077:             I_REMOTE_ADDRESS,
0078:             I_REMOTE_PORT
0079:         );
0080:     end;
0081: $body$;
0082: 
0083: 
0084: comment on function WRITE_MESSAGE_TO_TABLE(
0085:     I_FUNCTION text,
0086:     I_MESSAGE text,
0087:     I_LEVEL text,
0088:     I_PRESENT_USER name,
0089:     I_SESSION_USER name,
0090:     I_TRANSACTION_TIMESTAMP timestamp with time zone,
0091:     I_TRANSACTION_ID bigint,
0092:     I_SERVER_PID bigint,
0093:     I_REMOTE_ADDRESS inet,
0094:     I_REMOTE_PORT int
0095: ) is
0096:   'Write given parameters into the logging table. It is meant to be called in an autonomous transaction to and best done in a separate process to reduce impact on main transaction as much as possible.
0097: As colateral the ID of written record is returned.
0098: $Header: svn+ssh://thiemo@svn.code.sf.net/p/pglogger/code/functions/WRITE_MESSAGE_TO_TABLE.pg_sql 19 2018-06-15 20:24:38Z thiemo $';
0099: 
0100: 
0101: commit; -- unlike Oracle not all ddl commit implicitly
0102: 
0103: \echo End functions/WRITE_MESSAGE_TO_TABLE.pg_sql