function WRITE_MESSAGE

\echo Start functions/WRITE_MESSAGE.pg_sql

/** Write given parameters to different targets.
 *  The targets get defined in PROPERTY table.
 *
 *  @function WRITE_MESSAGE
 *  @return void
 *  @param in text I_MESSAGE Value for LOG.MESSAGE.
 *  @param in text I_LEVEL Value for LOG.LEVEL.
 *  @private
 *  @info This function is meant to be private. Its use outside pglogger is
 *        very limited at most. That's why it is marked as private eventhough
 *        it is visible to the outside. All of this is due to the fact that
 *        PostgreSQL 10 and earlier does not support packages.
 *  @version $Id: WRITE_MESSAGE.pg_sql 5 2018-03-30 16:41:07Z thiemo $
 *  @todo Once PostgreSQL supports packages, integrate this function into one.
 *        For the time being, it should get installed within a schema on its
 *        own and no execution grants should be given on it.
 *  @todo Once HyperSQL supports a license tag, convert the corresponding
 *        info tag into one.
 *  @copyright Thiemo Kellner, 2018 -
 *  @info License LGPLv3
 *  @webpage https://www.sourceforge.net/projects/pglogger
 */
create or replace function WRITE_MESSAGE(
    I_MESSAGE text,
    I_LEVEL text
)
  returns void
  language plpgsql
  stable as
$body$
    declare
        -- constants
        C_CALLER_FUNCTION constant name :=
          GET_CALLER_FUNCTION(3);
        C_PRESENT_USER constant name := current_user;
        C_SESSION_USER constant name := session_user;
        C_TRANSACTION_TIMESTAMP constant timestamp with time zone :=
          transaction_timestamp();
        C_TRANSACTION_ID constant bigint :=
          txid_current_if_assigned();
        C_SERVER_PID constant bigint := pg_backend_pid();
        C_REMOTE_ADDRESS constant inet := inet_client_addr();
        C_REMOTE_PORT constant int := inet_client_port();
        C_TIMESTAMPFORMAT constant text :=
          GET_PROPERTY_VALUE_STRING(
              I_PROPERTY_NAME => 'TIMESTAMP_DISPLAY_FORMAT'
          );
        C_INTEGERFORMAT constant text :=
          GET_PROPERTY_VALUE_STRING(
              I_PROPERTY_NAME => 'INTEGER_DISPLAY_FORMAT'
        );
        C_LB constant text :=
          GET_PROPERTY_VALUE_STRING(
              I_PROPERTY_NAME => 'LINEBREAK'
          );

        -- variables
        V_ENTRY text;
        -- concatenation with null results in null
        V_CALLER_FUNCTION text := coalesce(C_CALLER_FUNCTION, '');
        V_TRANSACTION_ID text :=
          trim(
              coalesce(
                  to_char(C_TRANSACTION_ID, C_INTEGERFORMAT),
                  ''
              )
          );
        V_SERVER_PID text := trim(to_char(C_SERVER_PID, C_INTEGERFORMAT));
        V_REMOTE_ADDRESS text := coalesce(host(C_REMOTE_ADDRESS), '');
        V_REMOTE_PORT text :=
          trim(
              coalesce(
                  to_char(C_REMOTE_PORT, C_INTEGERFORMAT),
                  ''
              )
          );
        V_DBLINK_CONNECT_STRING text;
        V_DBLINK_CONNECTION_NAME text;
        V_DBLINK_CONNECTION_NAMES text[];
        V_WAIT_FOR interval;
        V_QUERY text;
    begin
        if IS_LOGGING_TO_STANDARD() then
            V_ENTRY :=
              to_char(clock_timestamp(), C_TIMESTAMPFORMAT)
              || C_LB ||
              '   - Calling function: ' || V_CALLER_FUNCTION || C_LB ||
              '   - Current user: ' || C_PRESENT_USER || C_LB ||
              '   - Session user: ' || C_SESSION_USER || C_LB ||
              '   - Transaction timestamp: ' ||
              to_char(C_TRANSACTION_TIMESTAMP, C_TIMESTAMPFORMAT) || C_LB ||
              '   - Transaction ID: ' || V_TRANSACTION_ID || C_LB ||
              '   - Server process ID: ' || C_SERVER_PID || C_LB ||
              '   - Address of the remote connection: ' ||
              V_REMOTE_ADDRESS || C_LB ||
              '   - Port of the remote connection: ' ||
              V_REMOTE_PORT || C_LB ||
              '   - Message: ' || coalesce(I_MESSAGE, '');
            case I_LEVEL
                when 'DEBUG' then
                    raise debug '%', V_ENTRY;
                when 'LOG' then
                    raise log '%', V_ENTRY;
                when 'INFO' then
                    raise info '%', V_ENTRY;
                when 'NOTICE' then
                    raise notice '%', V_ENTRY;
                when 'WARNING' then
                    raise warning '%', V_ENTRY;
                when 'EXCEPTION' then
                    raise exception '%', V_ENTRY;
                else
                    raise exception '%', 'Unsupported level ' || I_LEVEL;
            end case;
        end if;
        if IS_LOGGING_TO_TABLE() then
            -- Background workers for autonomous transactions are not (yet)
            -- even modules. One had to write/compile code like the one of
            -- Robert Haas (available at
            -- https://github.com/vibhorkum/pg_background)
            -- As pglogger is meant to be usable out-of-the-box,
            -- pg_background_launch et al. is not a way to go.

            -- open the dblink if it does not yet exist
            V_DBLINK_CONNECTION_NAME :=
              GET_PROPERTY_VALUE_STRING(
                  I_PROPERTY_NAME => 'DBLINK_CONNECTION_NAME'
              );
            select dblink_get_connections() into V_DBLINK_CONNECTION_NAMES;
            if (
                V_DBLINK_CONNECTION_NAMES is null
             or V_DBLINK_CONNECTION_NAME != any(V_DBLINK_CONNECTION_NAMES)
            ) then
                V_DBLINK_CONNECT_STRING :=
                  GET_PROPERTY_VALUE_STRING(
                      I_PROPERTY_NAME => 'DBLINK_CONNECT_STRING'
                  );
                -- better to use dblink_connect_u with password file?
                perform dblink_connect(
                    V_DBLINK_CONNECTION_NAME,
                    V_DBLINK_CONNECT_STRING
                );
            else
                -- As long as the query results are not retrieved, the
                -- connection is not ready for another query no matter
                -- what dblink_is_busy says - it only checks, whether
                -- dblink still is preparing the results.
                -- Being so it seems there is no real "fire and
                -- forget" here. We therefore moved the retrieval to
                -- the beginning of the next call hoping to get something
                -- like fire and forget and to not lose the last call
                -- result in when the calling session ends. However,
                -- the result is being discarded and we do not implement
                -- another variable.
                perform * from  dblink_get_result(V_DBLINK_CONNECTION_NAME) as t(ID bigint);
--                 select * from dblink_get_result(V_DBLINK_CONNECTION_NAME) as T(ID bigint);
            end if;

            -- Send query asynchronously
            -- Use literal (%L) as it returns the value null as the unquoted
            -- string NULL.
            -- Is there a short way of casting timestamp with time zone?
            -- ::timestamp_with_time_zone does not work.
            V_QUERY := format(
                $s$select true $s$ || C_LB ||
                $s$  from %I( $s$ || C_LB ||
                $s$           I_FUNCTION => %L::text, $s$ || C_LB ||
                $s$           I_MESSAGE => %L::text, $s$ || C_LB ||
                $s$           I_LEVEL => %L::text, $s$ || C_LB ||
                $s$           I_PRESENT_USER => %L::name, $s$ || C_LB ||
                $s$           I_SESSION_USER => %L::name, $s$ || C_LB ||
                $s$           I_TRANSACTION_TIMESTAMP => $s$ ||
                $s$             cast(%L as timestamp with time zone), $s$ || C_LB ||
                $s$           I_TRANSACTION_ID => $s$ ||
                $s$             %L::bigint, $s$ || C_LB ||
                $s$           I_SERVER_PID => $s$ ||
                $s$             %L::bigint, $s$ || C_LB ||
                $s$           I_REMOTE_ADDRESS => $s$ ||
                $s$             %L::inet, $s$ || C_LB ||
                $s$           I_REMOTE_PORT => $s$ ||
                $s$             %L::int $s$ || C_LB ||
                $s$       ); $s$ || C_LB ||
                $s$commit $s$,
                'WRITE_MESSAGE_TO_TABLE',
                C_CALLER_FUNCTION,
                I_MESSAGE,
                I_LEVEL,
                C_PRESENT_USER,
                C_SESSION_USER,
                C_TRANSACTION_TIMESTAMP,
                C_TRANSACTION_ID,
                C_SERVER_PID,
                C_REMOTE_ADDRESS,
                C_REMOTE_PORT
            );
            -- send query when connection is ready
            V_WAIT_FOR :=
              GET_PROPERTY_VALUE_INTERVAL(
                  I_PROPERTY_NAME => 'BUSY_WAIT_INTERVAL'
              ); -- to avoid continuous re-querying, already queried here
            -- surprisingly, dblink_is_busy does not return boolean, but 0 for
            -- false
            while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop
                perform pg_sleep_for(V_WAIT_FOR);
            end loop;
            perform dblink_send_query(
                        V_DBLINK_CONNECTION_NAME,
                        V_QUERY
                    );
        end if;
    end;
$body$;

comment on function WRITE_MESSAGE(
    I_MESSAGE text,
    I_LEVEL text
) is
  'Write given parameters to different targets.
The targets get defined in PROPERTY table.
$Header: svn+ssh://thiemo@svn.code.sf.net/p/pglogger/code/functions/WRITE_MESSAGE.pg_sql 5 2018-03-30 16:41:07Z thiemo $';

commit; -- unlike Oracle not all ddl commit implicitly

\echo End functions/WRITE_MESSAGE.pg_sql