functions/SET_GENERAL_LOGGING_LEVEL.pg_sql

Functions

Source

001: \echo Start functions/SET_GENERAL_LOGGING_LEVEL.pg_sql
002: 
003: /** Set a new general logging level.
004:  *  It therefore updates LOGGING_LEVEL record in PROPERTY table. Please be
005:  *  aware that commiting the new value is not handled herein.
006:  *
007:  *  @function SET_GENERAL_LOGGING_LEVEL
008:  *  @return void
009:  *  @param in text I_LEVEL Value for general log level.
010:  *  @version $Id: SET_GENERAL_LOGGING_LEVEL.pg_sql 7 2018-06-12 04:15:42Z thiemo $
011:  *  @todo Once PostgreSQL supports packages, integrate this function into one.
012:  *        For the time being, it should get installed within a schema on its
013:  *        own and no execution grants should be given on it.
014:  *  @todo Once HyperSQL supports a license tag, convert the corresponding
015:  *        info tag into one.
016:  *  @copyright Thiemo Kellner, 2018 -
017:  *  @info License LGPLv3
018:  *  @webpage https://www.sourceforge.net/projects/pglogger
019:  */
020: create or replace function SET_GENERAL_LOGGING_LEVEL(
021:     I_LEVEL text
022: )
023:   returns void
024:   language plpgsql
025:   security definer
026:   volatile
027:   -- Include the hosting schema into search_path so that dblink
028:   -- can find the pglogger objects. There is no need to access
029:   -- objects in other schematas not covered with public.
030:   set search_path = :SCHEMA_NAME, public
031:   as
032: $body$
033:     declare
034:         C_LOGGING_LEVEL_PROPERTY_NAME constant text := 'LOGGING_LEVEL';
035:         V_ROW_COUNT bigint;
036:     begin
037:         update PROPERTY
038:            set PROPERTY_VALUE_STRING = I_LEVEL
039:          where PROPERTY_NAME = C_LOGGING_LEVEL_PROPERTY_NAME;
040:         get current diagnostics V_ROW_COUNT = ROW_COUNT;
041:         if V_ROW_COUNT < 1 then
042:             raise EXCEPTION 'Property record named "%" is missing',
043:               C_LOGGING_LEVEL_PROPERTY_NAME;
044:         elseif V_ROW_COUNT > 1 then
045:             raise EXCEPTION 'There are several property records named "%"',
046:               C_LOGGING_LEVEL_PROPERTY_NAME;
047:         end if;
048:     end;
049: $body$;
050: 
051: comment on function SET_GENERAL_LOGGING_LEVEL(I_MESSAGE text) is 'Set a new general logging level.
052: It therefore updates LOGGING_LEVEL record in PROPERTY table. Please be aware that commiting the new value is not handled herein.
053: $Header: svn+ssh://thiemo@svn.code.sf.net/p/pglogger/code/functions/SET_GENERAL_LOGGING_LEVEL.pg_sql 7 2018-06-12 04:15:42Z thiemo $';
054: 
055: commit; -- unlike Oracle not all ddl commit implicitly
056: 
057: \echo End functions/SET_GENERAL_LOGGING_LEVEL.pg_sql