-- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc.,
--           http://www.karlpinc.com/
-- Copyright (C) 2004-2011, Karl O. Pinc <kop@karlpinc.com>
--
--  This program is free software: you can redistribute it and/or modify
--  it under the terms of the GNU Affero General Public License as published
--  by the Free Software Foundation, either version 3 of the License, or
--  (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU Affero General Public License for more details.
--
--  You should have received a copy of the GNU Affero General Public License
--  along with this program.  If not, see <https://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@karlpinc.com>

-- DO NOT EDIT THIS FILE.  It was automatically generated.  Edit
-- the *.m4 files instead.  (Files _should_ be re-created by
-- typing 'make', with the appropriate target, at the command line.)






--
-- Support Tables
--

-- The key, description support tables.


CREATE TABLE IQ_TYPES (
  IQType TEXT
                PRIMARY KEY NOT NULL            CONSTRAINT "IQType: Cannot be empty or only whitespace characters"
                CHECK(btrim(IQType, E' \r\n\t\f\x0B') <> '')
                             CONSTRAINT "IQType: Cannot contain any whitespace characters"
                CHECK(IQType IS NULL OR
                      IQType !~ '( |\r|\n|\t|\f|\x0B)'),
  Description TEXT NOT NULL
                   CONSTRAINT "Description: Cannot be empty or only whitespace characters"
                CHECK(btrim(Description, E' \r\n\t\f\x0B') <> '')
);



CREATE TABLE IR_TYPES (
  IRType TEXT
                PRIMARY KEY NOT NULL            CONSTRAINT "IRType: Cannot be empty or only whitespace characters"
                CHECK(btrim(IRType, E' \r\n\t\f\x0B') <> '')
                             CONSTRAINT "IRType: Cannot contain any whitespace characters"
                CHECK(IRType IS NULL OR
                      IRType !~ '( |\r|\n|\t|\f|\x0B)'),
  Description TEXT NOT NULL
                   CONSTRAINT "Description: Cannot be empty or only whitespace characters"
                CHECK(btrim(Description, E' \r\n\t\f\x0B') <> '')
);


--
-- The primary tables
-- 

-- isok_queries
CREATE TABLE isok_queries (
    iqname TEXT PRIMARY KEY
                      CONSTRAINT "IQName: Cannot be empty or only whitespace characters"
                CHECK(btrim(IQName, E' \r\n\t\f\x0B') <> '')
                      CONSTRAINT "IQName: Cannot contain any whitespace characters"
                CHECK(IQName IS NULL OR
                      IQName !~ '( |\r|\n|\t|\f|\x0B)')
    , error BOOLEAN NOT NULL
    , type TEXT NOT NULL
      CONSTRAINT "Type must be an IQ_TYPES.IQType value"
                 REFERENCES iq_types
    , keep BOOLEAN NOT NULL
    , first_run TIMESTAMP(0)
    , last_run TIMESTAMP(0)
               CONSTRAINT "Last_Run must be >= First_Run"
                 CHECK(last_run IS NULL
                       OR first_run IS NULL
                       OR first_run <= last_run)
    , role name
    , search_path TEXT
    , query TEXT NOT NULL
                       CONSTRAINT "Query: Cannot be empty or only whitespace characters"
                CHECK(btrim(Query, E' \r\n\t\f\x0B') <> '')
    , comment TEXT NOT NULL
                          CONSTRAINT "Comment: Cannot be only whitespace characters"
                CHECK(Comment IS NULL OR Comment = '' OR btrim(Comment, E' \r\n\t\f\x0B') <> '')
);

-- isok_results
CREATE TABLE isok_results (
    irid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  , iqname TEXT NOT NULL
           CONSTRAINT "IQName must be an ISOK_QUERIES.IQName"
                       REFERENCES isok_queries
  , first_seen TIMESTAMP(0) NOT NULL
  , last_seen TIMESTAMP(0) NOT NULL
               CONSTRAINT "Last_Seen must be >= First_Seen"
                 CHECK(first_seen <= last_seen)
  , last_role name NOT NULL
  , last_schemas name[] NOT NULL
  , resolved TIMESTAMP(0)
             CONSTRAINT "Resolved must be >= First_Seen"
               CHECK(resolved IS NULL OR first_seen <= resolved)
  , deferred_to TIMESTAMP(0)
            CONSTRAINT "Either Resolved or Deferred_To or both must be NULL"
              CHECK(NOT(resolved IS NOT NULL AND deferred_to IS NOT NULL))
            CONSTRAINT "Deferred_To must be >= First_Seen"
               CHECK(deferred_to IS NULL OR first_seen <= deferred_to)
  , type TEXT
             CONSTRAINT "Type must be an IR_TYPES.IRType value"
                        REFERENCES ir_types
  , keep_until TIMESTAMP(0)
  , qr_id TEXT NOT NULL
                          CONSTRAINT "QR_ID: Cannot be empty or only whitespace characters"
                CHECK(btrim(QR_ID, E' \r\n\t\f\x0B') <> '')
  , qr_message TEXT NOT NULL
                          CONSTRAINT "QR_Message: Cannot be empty or only whitespace characters"
                CHECK(btrim(QR_Message, E' \r\n\t\f\x0B') <> '')
  , qr_extra JSONB
  , notes TEXT NOT NULL
                     CONSTRAINT "Notes: Cannot be only whitespace characters"
                CHECK(Notes IS NULL OR Notes = '' OR btrim(Notes, E' \r\n\t\f\x0B') <> '')
  , CONSTRAINT "The combination of IQName and QR_ID must be unique"
      UNIQUE (iqname, qr_id)
);
-- pg_isok  A query centered monitoring tool for PostgreSQL
-- Copyright (C) 2015, 2025 The Meme Factory, Inc.  http://www.karlpinc.com/
--
--  This program is free software: you can redistribute it and/or modify
--  it under the terms of the GNU Affero General Public License as published
--  by the Free Software Foundation, either version 3 of the License, or
--  (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU Affero General Public License for more details.
--
--  You should have received a copy of the GNU Affero General Public License
--  along with this program.  If not, see <https://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@karlpinc.com>
--
-- Remarks:
--

-- Create indexes on all the Isok tables.

-- Remarks:

-- isok_queries
-- This first index is a simple way to enforce case-insensitive
-- uniqueness, but is not otherwise useful.
-- "lower() is not exactly the same as case-folding, because Unicode
-- and human language, but it is close enough.
CREATE UNIQUE INDEX "ISOK_QUERIES.IQName is case-insensitively unique"
  ON isok_queries (lower(iqname));
CREATE INDEX isok_queries_type
  ON isok_queries (type);
CREATE INDEX isok_queries_last_run
  ON isok_queries (last_run);

-- isok_results
-- There is a unique index on iqname + qr_id, created by constraint.
-- This should be good enough when searching on iqname.
CREATE INDEX isok_results_first_seen
  ON isok_results (first_seen);
CREATE INDEX isok_results_last_seen
  ON isok_results (last_seen);
CREATE INDEX isok_results_resolved
  ON isok_results (resolved)
  WHERE resolved IS NOT NULL;
CREATE INDEX isok_results_deferred_to
  ON isok_results (deferred_to)
  WHERE deferred_to IS NOT NULL;
CREATE INDEX isok_results_type
  ON isok_results (type);
CREATE INDEX isok_results_qr_id
  ON isok_results (qr_id);

--
-- Support tables
--

-- All support tables require their keys be case-insensitive unique.
-- See the comment on INDEX isok_queries_caseunique_iqname, above.


-- iq_types
CREATE UNIQUE INDEX "IQ_TYPES.IQType is case-insensitively unique"
  ON iq_types (lower(iqtype));
CREATE UNIQUE INDEX "IQ_TYPES.Description is case-insensitively unique"
  ON iq_types (lower(description));

-- ir_types
CREATE UNIQUE INDEX "IR_TYPES.IRType is case-insensitively unique"
  ON ir_types (lower(irtype));
CREATE UNIQUE INDEX "IR_TYPES.Description is case-insensitively unique"
  ON ir_types (lower(description));

-- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc.,
--           http://www.karlpinc.com/
-- Copyright (C) 2004-2011, Karl O. Pinc <kop@karlpinc.com>
--
--  This program is free software: you can redistribute it and/or modify
--  it under the terms of the GNU Affero General Public License as published
--  by the Free Software Foundation, either version 3 of the License, or
--  (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU Affero General Public License for more details.
--
--  You should have received a copy of the GNU Affero General Public License
--  along with this program.  If not, see <https://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@karlpinc.com>

-- DO NOT EDIT THIS FILE.  It was automatically generated.  Edit
-- the *.m4 files instead.  (Files _should_ be re-created by
-- typing 'make', with the appropriate target, at the command line.)

--  
-- isok_queries
--

CREATE FUNCTION isok_queries_update_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
  -- Function for isok_queries update triggers
  --
  -- Copyright (C) 2015, 2025 The Meme Factory, Inc., http://www.karlpinc.com/
  -- Distributed under the GNU Affero General Public License, version 3 or later.

  
  IF NEW.IQName <> OLD.IQName THEN
    -- IQName has changed
    RAISE EXCEPTION integrity_constraint_violation USING
          MESSAGE = 'Error on UPDATE of @extschema@.ISOK_QUERIES'
        , DETAIL =  'Value (IQName) = (' || OLD.IQName
                    || '): @extschema@.ISOK_QUERIES.IQName cannot be changed';
    RETURN NULL;
  END IF;


  IF OLD.error <> NEW.error AND NEW.error THEN
    PERFORM 1
      FROM @extschema@.isok_results
      WHERE @extschema@.isok_results.iqname = NEW.iqname
            AND @extschema@.isok_results.resolved IS NOT NULL;
    IF FOUND THEN
      RAISE EXCEPTION integrity_constraint_violation USING
            MESSAGE = 'Error on UPDATE of ISOK_QUERIES'
          , DETAIL = 'Key (IQName) = (' || NEW.iqname
                     || '): Value (Error) = (' || NEW.error
                     || '): Cannot make into an error when there are already'
                     || ' ISOK_RESULTS rows with a TRUE Resolved value';
      RETURN NULL;
    END IF;
  END IF;

  RETURN NULL;
  END;
$$;


CREATE TRIGGER isok_queries_update_trigger
  AFTER UPDATE
  ON isok_queries FOR EACH ROW
  EXECUTE PROCEDURE isok_queries_update_func();

-- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc.,
--           http://www.karlpinc.com/
-- Copyright (C) 2004-2011, Karl O. Pinc <kop@karlpinc.com>
--
--  This program is free software: you can redistribute it and/or modify
--  it under the terms of the GNU Affero General Public License as published
--  by the Free Software Foundation, either version 3 of the License, or
--  (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU Affero General Public License for more details.
--
--  You should have received a copy of the GNU Affero General Public License
--  along with this program.  If not, see <https://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@karlpinc.com>

-- DO NOT EDIT THIS FILE.  It was automatically generated.  Edit
-- the *.m4 files instead.  (Files _should_ be re-created by
-- typing 'make', with the appropriate target, at the command line.)

--  
-- isok_results
--

CREATE FUNCTION isok_results_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  AS $$
  BEGIN
  -- Function for isok_results insert and update triggers
  --
  -- Copyright (C) 2015, 2025 The Meme Factory, Inc., http://www.karlpinc.com/
  -- Distributed under the GNU Affero General Public License, version 3 or later.

  IF TG_OP = 'UPDATE' THEN
    
  IF NEW.IRID <> OLD.IRID THEN
    -- IRID has changed
    RAISE EXCEPTION integrity_constraint_violation USING
          MESSAGE = 'Error on UPDATE of @EXTSCHEMA@.ISOK_RESULTS'
        , DETAIL =  'Value (IRID) = (' || OLD.IRID
                    || '): @EXTSCHEMA@.ISOK_RESULTS.IRID cannot be changed';
    RETURN NULL;
  END IF;

  END IF;

  -- Errors are not allowed to be resolved.
  IF NEW.resolved IS NOT NULL THEN
    PERFORM 1
      FROM @extschema@.isok_queries
      WHERE @extschema@.isok_queries.iqname = NEW.iqname
            AND @extschema@.isok_queries.error;
    IF FOUND THEN
      RAISE EXCEPTION integrity_constraint_violation USING
            MESSAGE = 'Error on ' || TG_OP || ' of ISOK_QUERIES'
          , DETAIL = 'Key (IRID) = (' || NEW.irid
                     || '): Value (IQName) = (' || NEW.iqname
                     || '): Value (QR_ID) = (' || NEW.qr_id
                     || '): Value (Resolved) = (' || NEW.resolved
                     || '): The INTEGRITY_QUERY ('
                     || NEW.iqname
                     || ') produces errors, these may not be marked resolved';
      RETURN NULL;
    END IF;
  END IF;

  RETURN NULL;
  END;
$$;


CREATE TRIGGER isok_results_trigger
  AFTER INSERT OR UPDATE
  ON isok_results FOR EACH ROW
  EXECUTE PROCEDURE isok_results_func();
-- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc.,
--           http://www.karlpinc.com/
-- Copyright (C) 2004-2011, Karl O. Pinc <kop@karlpinc.com>
--
--  This program is free software: you can redistribute it and/or modify
--  it under the terms of the GNU Affero General Public License as published
--  by the Free Software Foundation, either version 3 of the License, or
--  (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU Affero General Public License for more details.
--
--  You should have received a copy of the GNU Affero General Public License
--  along with this program.  If not, see <https://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@karlpinc.com>

-- DO NOT EDIT THIS FILE.  It was automatically generated.  Edit
-- the *.m4 files instead.  (Files _should_ be re-created by
-- typing 'make', with the appropriate target, at the command line.)














CREATE OR REPLACE FUNCTION run_isok_queries()
  RETURNS TABLE(
    irid              @extschema@.isok_results.irid%TYPE
  , iqname            @extschema@.isok_results.iqname%TYPE
  , first_seen        @extschema@.isok_results.first_seen%TYPE
  , last_seen         @extschema@.isok_results.last_seen%TYPE
  , last_role         @extschema@.isok_results.last_role%TYPE
  , last_schemas      @extschema@.isok_results.last_schemas%TYPE
  , deferred_to       @extschema@.isok_results.deferred_to%TYPE
  , type              @extschema@.isok_results.type%TYPE
  , keep_until        @extschema@.isok_results.keep_until%TYPE
  , qr_id             @extschema@.isok_results.qr_id%TYPE
  , qr_message        @extschema@.isok_results.qr_message%TYPE
  , qr_extra          @extschema@.isok_results.qr_extra%TYPE
  , notes             @extschema@.isok_results.notes%TYPE
)
  LANGUAGE plpgsql
  SECURITY INVOKER
  AS $$
  -- Run all queries in ISOK_QUERIES.
  --
  -- Copyright (C) 2015, 2025 The Meme Factory, Inc.  http://www.karlpinc.com/
  -- Distributed under the GNU Affero General Public License, version 3 or later.
  --
  -- Syntax: run_isok_queries()
  --
  -- Arguments:
  --
  -- Remarks:
  --   Isok version: 1.4.1
  --
  -- Bugs:

  DECLARE

  BEGIN

  RETURN QUERY EXECUTE 'SELECT * FROM @extschema@.run_isok_queries(
                          $RIQ$SELECT @extschema@.isok_queries.iqname
                                 FROM @extschema@.isok_queries
                                 ORDER BY @extschema@.isok_queries.iqname$RIQ$
                                 )';
  END;
$$;


CREATE OR REPLACE FUNCTION run_isok_queries(query_arg TEXT)
  RETURNS TABLE(
    irid              @extschema@.isok_results.irid%TYPE
  , iqname            @extschema@.isok_results.iqname%TYPE
  , first_seen        @extschema@.isok_results.first_seen%TYPE
  , last_seen         @extschema@.isok_results.last_seen%TYPE
  , last_role         @extschema@.isok_results.last_role%TYPE
  , last_schemas      @extschema@.isok_results.last_schemas%TYPE
  , deferred_to       @extschema@.isok_results.deferred_to%TYPE
  , type              @extschema@.isok_results.type%TYPE
  , keep_until        @extschema@.isok_results.keep_until%TYPE
  , qr_id             @extschema@.isok_results.qr_id%TYPE
  , qr_message        @extschema@.isok_results.qr_message%TYPE
  , qr_extra          @extschema@.isok_results.qr_extra%TYPE
  , notes             @extschema@.isok_results.notes%TYPE
)
  LANGUAGE plpgsql
  SECURITY INVOKER
  AS $$
  -- Run the queries in ISOK_QUERIES that have an IQName returned by
  -- the SQL statement given as an argument.
  --
  -- Copyright (C) 2015, 2025 The Meme Factory, Inc.  http://www.karlpinc.com/
  -- Distributed under the GNU Affero General Public License, version 3 or later.
  --
  -- Syntax: run_isok_queries(query)
  --
  -- Arguments:
  --   query  A query returning 1 column, of INTEGRITY_QUERY.IQName values.
  --
  -- Remarks:
  --
  -- Bugs:

  DECLARE

    -- From query results
    this_iqname @extschema@.isok_queries.iqname%TYPE;
    this_keep @extschema@.isok_queries.keep%TYPE;
    saved_role @extschema@.isok_queries.role%TYPE;
    saved_search_path @extschema@.isok_queries.search_path%TYPE;
    saved_query @extschema@.isok_queries.query%TYPE;

    -- Dynamic commands
    starting_search_path TEXT;
    role_used name;
    become_starting_role TEXT;
    become_saved_role TEXT;

    -- Saved query results
    id TEXT;
    msg TEXT;
    extra JSONB;

    -- Exception handling
    my_message_text TEXT;
    my_pg_exception_detail TEXT;
    my_pg_exception_hint TEXT;
    my_pg_exception_context TEXT;

    exception_hint TEXT;
    exception_caught BOOLEAN := FALSE;

  BEGIN
    -- Run the supplied query and iterate over the saved queries.
    FOR this_iqname IN EXECUTE query_arg LOOP

      -- Keep track of which query results we receive from the query
      CREATE TEMP TABLE _riq_qr_ids AS
        SELECT @extschema@.isok_results.qr_id
          FROM @extschema@.isok_results
        WITH NO DATA;

      -- Get the saved query
      SELECT
             @extschema@.isok_queries.query, @extschema@.isok_queries.keep
           , @extschema@.isok_queries.search_path
           , @extschema@.isok_queries.role
        INTO
             saved_query                   , this_keep
           , saved_search_path
           , saved_role
        FROM @extschema@.isok_queries
        WHERE @extschema@.isok_queries.iqname = this_iqname;

      IF NOT FOUND THEN
        RAISE EXCEPTION data_exception USING
          MESSAGE = 'Error in run_isok_queries(TEXT)'
          , DETAIL = 'Supplied query returned a value ('
                     || CASE WHEN this_iqname IS NULL THEN 'NULL' ELSE this_iqname::TEXT END

                     || ') that is not an existing'
                     || ' ISOK_QUERIES.IQName value';
        RETURN;
      END IF;

      IF saved_search_path IS NOT NULL THEN
        -- Use the given search path while executing the query
        starting_search_path := current_setting('search_path');

        BEGIN
          EXECUTE 'SET LOCAL search_path TO ' || saved_search_path || ';';

        EXCEPTION
          WHEN syntax_error_or_access_rule_violation THEN
            
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

            exception_caught := TRUE;
            RAISE EXCEPTION data_exception USING
              MESSAGE = 'run_isok_queries:'
                        || ' Error setting search_path before executing an'
                        || ' ISOK_QUERIES.Query'
              , DETAIL = 'Problem in the search_path with IQName = ('
                         || this_iqname || E'):\n'
                         || 'The error is: ' || my_message_text
                         || my_pg_exception_detail
                         || my_pg_exception_hint
                         || E'\nThe ISOK_QUERIES.Search_Path is:\n'
                         || saved_search_path
                         || my_pg_exception_context;
        END;
      END IF;

      IF saved_role IS NULL THEN
        role_used := current_user;
      ELSE
        -- The query has an associated role
        role_used := saved_role;

        -- Set the role, if any

        -- Save commands to switch back and forth between roles
        become_starting_role := 'SET LOCAL ROLE TO ' || current_user || ';';
        become_saved_role := 'SET LOCAL ROLE TO ' || saved_role || ';';

        BEGIN
          EXECUTE become_saved_role;

        EXCEPTION
          -- We want all of these to trap most that could go wrong:
          -- data_exception  (22000)  no such role, etc.
          -- syntax_error_or_access_rule_violation (42000)
          WHEN data_exception
               OR syntax_error_or_access_rule_violation THEN
            
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

            exception_caught := TRUE;
            RAISE EXCEPTION data_exception USING
              MESSAGE = 'run_isok_queries:'
                        || ' Error setting role before executing an'
                        || ' ISOK_QUERIES.Query'
              , DETAIL = 'Problem in the role with IQName = ('
                         || this_iqname || E'):\n'
                         || 'The error is: ' || my_message_text
                         || my_pg_exception_detail
                         || my_pg_exception_hint
                         || E'\nThe ISOK_QUERIES.Role is:\n'
                         || saved_role
                         || my_pg_exception_context;
        END;
      END IF;

      BEGIN
        FOR id, msg, extra IN EXECUTE saved_query LOOP
          IF saved_role IS NOT NULL THEN
            -- We don't know that the role used to execute the query
            -- has permissions to the ISOK_RESULTS table.
            EXECUTE become_starting_role;
          END IF;

          -- Remember that we processed the id.
          INSERT INTO _riq_qr_ids (qr_id)
            VALUES (id);

          BEGIN
            -- Make a new isok_results row.
            INSERT INTO @extschema@.isok_results
              (iqname            , first_seen       , last_seen
                     , last_role
                     , last_schemas
                     , keep_until
                     , qr_id, qr_message, qr_extra
                     , notes)
              VALUES (this_iqname, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    , role_used
                    , current_schemas(TRUE)
                    , CASE WHEN this_keep
                        THEN 'infinity'::TIMESTAMP
                        ELSE NULL
                      END
                    , id, msg, extra
                    , '')
              ON CONFLICT
                ON CONSTRAINT "The combination of IQName and QR_ID must be unique"
                -- Update the existing isok_results row.
                DO UPDATE
                  SET last_seen = CURRENT_TIMESTAMP
                    , last_role = role_used
                    , last_schemas = current_schemas(TRUE)
                    , qr_message = msg
                    , qr_extra = extra
                  WHERE @extschema@.isok_results.iqname = this_iqname
                        AND @extschema@.isok_results.qr_id = id;
          EXCEPTION
            -- We want a different error message here because the
            -- context is useless, so don't include that.
            -- When something goes wrong here it's usually the values
            -- returned by the supplied query.  Trap everything,
            -- just in case.
            WHEN feature_not_supported
             OR sql_statement_not_yet_complete
             OR data_exception
             OR integrity_constraint_violation
             OR invalid_sql_statement_name
             OR sql_routine_exception
             OR syntax_error_or_access_rule_violation THEN
              
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

              exception_caught := TRUE;
              RAISE EXCEPTION data_exception USING
                MESSAGE = 'run_isok_queries:'
                          || ' Error storing the results of an'
                          || ' ISOK_QUERIES.Query'
                , DETAIL = 'Problem in the saved query with IQName = ('
                           || this_iqname || E'):\n'
                           || 'The error is: ' || my_message_text
                           || my_pg_exception_detail
                           || my_pg_exception_hint
                           || E'\nThe query is:\n'
                           || saved_query;
          END;

          IF saved_role IS NOT NULL THEN
            -- Put the desired effective role back in place for
            -- continued query execution.  There is no documentation
            -- of how row-level security policies execution is
            -- affected by changing the effective role while a query
            -- executes.  So make sure the effective role does not
            -- change while the query executes.
            EXECUTE become_saved_role;
          END IF;
        END LOOP;
      EXCEPTION
        -- We want all of these to trap most that could go wrong when a
        -- user-supplied bit of sql is executed
        WHEN feature_not_supported
             OR sql_statement_not_yet_complete
             OR data_exception
             OR integrity_constraint_violation
             OR invalid_sql_statement_name
             OR sql_routine_exception
             OR syntax_error_or_access_rule_violation THEN
          
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

          exception_caught := TRUE;
          RAISE EXCEPTION data_exception USING
            MESSAGE = 'run_isok_queries:'
                      || ' Error executing stored ISOK_QUERIES.Query'
            , DETAIL = 'Problem in the saved query with IQName = ('
                       || this_iqname || E'):\n'
                       || 'The error is: ' || my_message_text
                       || my_pg_exception_detail
                       || my_pg_exception_hint
                       || E'\nThe query is:\n'
                       || saved_query
                       || my_pg_exception_context;
      END;

      -- Reset the role to its initial state
      IF saved_role IS NOT NULL THEN
        EXECUTE become_starting_role;
      END IF;

      -- Reset the search path to its initial state
      IF saved_search_path IS NOT NULL THEN
        EXECUTE 'SET LOCAL search_path TO ' || starting_search_path || ';';
      END IF;

      -- Note that we've processed the saved query.
      UPDATE @extschema@.isok_queries
        SET last_run = CURRENT_TIMESTAMP
          , first_run = COALESCE(first_run, CURRENT_TIMESTAMP)
        WHERE @extschema@.isok_queries.iqname = this_iqname;

      -- Delete old query results that are not found this time.
      CREATE UNIQUE INDEX _riq_qr_ids_qr_id
        ON _riq_qr_ids (qr_id);

      DELETE FROM @extschema@.isok_results
        WHERE @extschema@.isok_results.iqname = this_iqname
              AND (@extschema@.isok_results.keep_until IS NULL
                   OR @extschema@.isok_results.keep_until
                       <= CURRENT_TIMESTAMP::TIMESTAMP(0))
              AND NOT EXISTS(
                  SELECT 1
                    FROM _riq_qr_ids
                    WHERE @extschema@.isok_results.qr_id
                          = _riq_qr_ids.qr_id);

      -- Cleanup
      DROP TABLE _riq_qr_ids CASCADE;

      -- Return the rows found with their new values.
      RETURN QUERY
        SELECT @extschema@.isok_results.irid
             , @extschema@.isok_results.iqname
             , @extschema@.isok_results.first_seen
             , @extschema@.isok_results.last_seen
             , @extschema@.isok_results.last_role
             , @extschema@.isok_results.last_schemas
             , @extschema@.isok_results.deferred_to
             , @extschema@.isok_results.type
             , @extschema@.isok_results.keep_until
             , @extschema@.isok_results.qr_id
             , @extschema@.isok_results.qr_message
             , @extschema@.isok_results.qr_extra
             , @extschema@.isok_results.notes
          FROM @extschema@.isok_results
          WHERE @extschema@.isok_results.iqname = this_iqname
                AND @extschema@.isok_results.resolved IS NULL
                AND (@extschema@.isok_results.deferred_to IS NULL
                     OR @extschema@.isok_results.deferred_to
                        <= CURRENT_TIMESTAMP::TIMESTAMP(0));
    END LOOP; -- Over iqname

  EXCEPTION
    -- We want all of these to trap most that could go wrong when a
    -- user-supplied bit of sql is executed
    WHEN feature_not_supported
             OR sql_statement_not_yet_complete
             OR data_exception
             OR integrity_constraint_violation
             OR invalid_sql_statement_name
             OR sql_routine_exception
             OR syntax_error_or_access_rule_violation THEN
      IF exception_caught THEN
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL;
        RAISE EXCEPTION data_exception USING
            MESSAGE = my_message_text
          , DETAIL = my_pg_exception_detail;
      ELSE
        
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;


        PERFORM 1
          FROM @extschema@.isok_queries
          WHERE isok_queries.iqname = query_arg;
        IF FOUND THEN
          exception_hint :=
            E'Perhaps you meant to write something like:\n'
            || 'SELECT * FROM @extschema@.run_isok_queries('
            || E'$\$VALUES (\'' || query_arg || E'\')$\$);';
        ELSE
          exception_hint :=
            'Try executing the SQL query (' || query_arg
            || ') and check that it returns the'
            || ' @extschema@.isok_queries.iqname values of the queries you'
            || ' wish to run.  Then, call run_isok_queries() with your'
            || ' corrected argument.  (You will probably'
            || ' need to dollar-quote your argument.)';
        END IF;

        RAISE EXCEPTION data_exception USING
          MESSAGE = 'run_isok_queries:'
                    || ' Error executing the supplied argument, which should'
                    || ' be an SQL query that returns isok_queries.iqname'
                    || ' values'
          , DETAIL = E'\nThe error is: ' || my_message_text
                     || my_pg_exception_detail
                     || my_pg_exception_hint
                     || E'\nThe query is:\n'
                     || query_arg
                     || my_pg_exception_context
          , HINT = exception_hint;
      END IF;
   -- WHEN OTHERS THEN
   --   RAISE EXCEPTION data_exception USING
   --     MESSAGE = 'SQLSTATE ' || SQLSTATE || ': SQLERRM ' || SQLERRM;
  END;
$$;
-- pg_isok  A query centered monitoring tool for PostgreSQL
-- Copyright (C) 2025 The Meme Factory, Inc.  http://www.karlpinc.com/
--
--  This program is free software: you can redistribute it and/or modify
--  it under the terms of the GNU Affero General Public License as published
--  by the Free Software Foundation, either version 3 of the License, or
--  (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU Affero General Public License for more details.
--
--  You should have received a copy of the GNU Affero General Public License
--  along with this program.  If not, see <https://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@karlpinc.com>
--
-- Remarks:
--   Hand-written comments.
--   Note that comments extracted from the documentation are appended
--   to this file.
--
--   Line breaks at 50 columns, since that's what format_comments.gawk does.

--
-- Support table columns.
--

-- IQ_TYPES
COMMENT ON COLUMN iq_types.iqtype IS
  'A classification code attached to a query in the
ISOK_QUERIES table.';
  
COMMENT ON COLUMN iq_types.description IS
  'A description of the IQType code.';

-- IRTypes
COMMENT ON COLUMN ir_types.irtype IS
  'A classification code attached to a query result
row in the ISOK_RESULTS table.';

COMMENT ON COLUMN ir_types.description IS
  'A description of the IRType code.';

--
-- Comment the functions used by people
--

-- run_isok_queries()

COMMENT ON FUNCTION run_isok_queries() IS
  'Execute all the queries in the ISOK_QUERIES table and
return a table of the results.';

COMMENT ON FUNCTION run_isok_queries(query TEXT) IS
  'Execute the supplied query, which produces a single
column of ISOK_QUERIES.IQName values, and then
execute all the queries in the ISOK_QUERIES table
with those IQName values, returning a table of the
result of the executed queries.';

--
-- Comment the functions used by triggers
--

COMMENT ON FUNCTION isok_queries_update_func() IS
  'Internal function: Implements an AFTER UPDATE FOR EACH
ROW trigger on ISOK_QUERIES';

COMMENT ON FUNCTION isok_results_func() IS
  'Internal function: Implements an AFTER INSERT OR UPDATE
FOR EACH ROW trigger on ISOK_RESULTS';

--
-- Everything that follows is extracted from the documentation.
--
COMMENT ON TABLE ISOK_QUERIES IS
$isok_comment$The ISOK_QUERIES table contains one row for every
query used to search for database integrity
issues.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.IQName IS
$isok_comment$A unique name for the query.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Error IS
$isok_comment$TRUE when the query finds conditions that are
errors, FALSE when the query finds conditions that
are warnings.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Type IS
$isok_comment$Code classifying the query. The legal values for
this column are defined by the IQ_TYPES support
table.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.First_Run IS
$isok_comment$Date and time the query was first run by Isok.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Last_Run IS
$isok_comment$Date and time the query was most recently run by
Isok.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Keep IS
$isok_comment$This column controls the value placed in the
ISOK_RESULTS.Keep_Until column when
run_isok_queries() inserts new rows in
ISOK_RESULTS.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Role IS
$isok_comment$The PostgreSQL role to use to run the query.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Search_Path IS
$isok_comment$The PostgreSQL schema search_path to have in
effect when the query is run.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Query IS
$isok_comment$A query which checks for database integrity
violations.$isok_comment$;

COMMENT ON COLUMN ISOK_QUERIES.Comment IS
$isok_comment$A comment on the query.$isok_comment$;

COMMENT ON TABLE ISOK_RESULTS IS
$isok_comment$The ISOK_RESULTS table contains one row for every
database integrity problem discovered by the
queries in ISOK_QUERIES.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.IRID IS
$isok_comment$This column uniquely identifies the row containing
the$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.IQName IS
$isok_comment$The ISOK_QUERIES.IQName value identifying the
query which produced the result.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.First_Seen IS
$isok_comment$Date and time the query result was first produced
by Isok.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Last_Seen IS
$isok_comment$Date and time the query result was most recently
produced by Isok.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Last_Role IS
$isok_comment$The role (user) which was the current role when
the query was last executed.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Last_Schemas IS
$isok_comment$All schemas that were, implicitly or not, in the
search_path, and also available to the Last_Role,
when the result was returned.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Resolved IS
$isok_comment$Date and time the query result was resolved; that
is, marked not a concern.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Deferred_To IS
$isok_comment$Isok suppresses display of the result when the
current time is before this time.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Type IS
$isok_comment$Code classifying the query result. The legal
values for this column are defined by the IR_TYPES
support table.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Keep_Until IS
$isok_comment$This column controls whether or not
run_isok_queries() deletes the row when the
ISOK_QUERIES.Query is re-run and the query does
not return the row's QR_ID.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.QR_ID IS
$isok_comment$This is a unique, unique per query that is,
identifier for the query result.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.QR_Message IS
$isok_comment$This is the message, the second column, produced
by the most recent execution of the
ISOK_QUERIES.Query.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.QR_Extra IS
$isok_comment$The value of the third, optional, column returned
by most recent execution of the query. This may
contain any JSON deemed useful.$isok_comment$;

COMMENT ON COLUMN ISOK_RESULTS.Notes IS
$isok_comment$Any notes regarding this particular query result.$isok_comment$;

COMMENT ON TABLE IQ_TYPES IS
$isok_comment$IQ_TYPES contains one row for every code used to
classify database integrity queries.$isok_comment$;

COMMENT ON TABLE IR_TYPES IS
$isok_comment$IR_TYPES contains one row for every code used to
classify or explain sets of database integrity
problems, problems discovered by Isok's queries.$isok_comment$;

