run_isok_queries
Prev | The Isok Functions (Activating Isok) | Next

## Name

run_isok_queries — execute one or more of the queries stored in the ISOK_QUERIES table

## Synopsis

`TABLE (irid, iqname, first_seen, last_seen, category, qr_id, qr_message, notes) run_isok_queries (` | `)`; |

`TABLE (irid, iqname, first_seen, last_seen, category, qr_id, qr_message, notes) run_isok_queries (` | iqname_query`)`; |

`TEXT iqname_query` ;

## Input

### iqname_query

The text of an SQL query. The query must return a single column of ISOK_QUERIES.IQName values.

## Description

A function which runs the queries stored in the ISOK_QUERIES table, returns the output of the stored queries, and stores the results in the ISOK_RESULTS table. Because the function returns rows and columns it must be invoked in the FROM clause of a SELECT statement. (See the Examples below.)

The function may be called in one of two ways. When called with no arguments all of the queries in ISOK_QUERIES are run. When called with the text of an SQL query, a query which returns a single column containing ISOK_QUERIES.IQName values, the function runs only those queries.

### Tip

Use PostgreSQL's dollar quoting when supplying a query to run_isok_queries().

The function returns a set of columns with multiple rows, a table. So it is expected to be used in the FROM clause of a SELECT statement. The columns returned by the function are the columns of the ISOK_RESULTS table, excepting the Resolved column.

The rows returned by the function are those of the newly updated ISOK_RESULTS table, excepting those rows with a non-`NULL` Resolved column or those rows with a Deferred_To value that is in the future. Only those rows that are related to the executed queries (in ISOK_QUERIES) are returned. So, when called with no arguments the function returns all warnings that have not been resolved and all errors. When called with a query that selects specific ISOK_QUERIES to execute, only the unresolved warnings and errors discovered by the executed ISOK_QUERIES are returned.

## The Record of Query Execution

Running an ISOK_QUERIES.Query does more than add new rows to the ISOK_RESULTS table. Updates are made to existing rows to record and track the query execution's results.

The ISOK_QUERIES.Last_Run value is updated.

On ISOK_RESULTS, the rows to update are found by matching the ISOK_RESULTS.IQName value with the ISOK_QUERIES.IQName of the executed query, while also matching the QR_ID value with the value returned in the first column of the executed query. The columns updated are: Last_Seen , Last_Role , Last_Schemas , QR_Message , and QR_Extra.

Because the record of the results produced by Isok queries are updated, a query may be refined over time to produce enough information to resolve the reported issues.

Even though the execution of run_isok_queries() does not return rows that are resolved, all rows returned by an executed query have all the aforementioned columns updated to new values. Whether a row is returned or not does not matter, the update occurs anyway.

## Deletion of Old Results

If an existing ISOK_RESULTS row matches the IQName value of the executed query and there is no corresponding QR_ID value returned by the executed query, and the value of ISOK_RESULTS.Keep_Until is either `NULL` or CURRENT_TIMESTAMP[5] is not earlier than ISOK_RESULTS.Keep_Until then the ISOK_RESULTS row is deleted. This empties the ISOK_RESULTS table of errors and warnings that no longer apply to the current state of the database.

If the query returns warnings, this deletion behavior does not depend upon whether or not the warning is resolved.

## Examples

The following example runs all the queries in ISOK_QUERIES, displays all the errors and all the unresolved warnings (unless the error or warning has been deferred), ordered first by the name of the query, within that showing newer problems first, and within that ordered by warning id.

Example 2. Executing all ISOK_QUERIES

SELECT *
FROM run_isok_queries() AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;

The following example runs a single saved query with an ISOK_QUERIES.IQName of `mycheck` and displays any of these sorts of problems found, ordered as in the previous example. This example also demonstrates how to use dollar quoting to give a query to run_isok_queries and thereby avoid problems having to do with trying to nest regular quotes.

Example 3. Executing a single ISOK_QUERIES.Query

SELECT *
FROM run_isok_queries($$SELECT 'mycheck'$$) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;

The following example runs multiple specific queries, those with an ISOK_QUERIES.IQName of `mycheck`, `yourcheck`, and `theircheck`, and displays any of these sorts of problems found, ordered as in the previous example. As before, dollar quoting is used to quote the query which produces the IQNames.

Example 4. Executing many specific ISOK_QUERIES.Query-s

SELECT *
FROM run_isok_queries($$VALUES ('mycheck')
, ('yourcheck')
, ('theircheck')$$) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;

The following example runs all the queries of the bdate type and displays any of these sorts of problems found, ordered as in the previous example. Again, dollar quoting is used.

Example 5. Executing ISOK_QUERIES of the “bdate” type

SELECT *
FROM run_isok_queries(
$$SELECT isok_queries.iqname
FROM isok_queries
WHERE isok_queries.type = 'bdate'$$
) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;

[5] The time the current transaction started, which, if a transaction was not explicitly started, is the time the database engine received the current SQL statement from the client and began execution.

Prev | Up | Next
The Isok Functions (Activating Isok) | Home | A. Security Considerations

Page generated: 2025-06-02T22:25:00-05:00.