run_isok_queries
Prev | The Functions (Running 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, type, qr_id, qr_message, notes) run_isok_queries (` | `)`; |

`TABLE (irid, iqname, first_seen, last_seen, type, 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 -- excepting resolved and deferred rows, and stores the results in the ISOK_RESULTS table. Because the function returns rows and columns it is expected 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 returned by the queries the function executes, excepting resolved and deferred rows. A row is resolved or deferred when there is a row in ISOK_RESULTS that has a IQName value matching that of the query and a QR_ID value matching that of the row, and that ISOK_RESULTS row has a non-`NULL` Resolved column or a Deferred_To value that is in the future.

So, when called with no arguments the function returns all warning conditions that currently exist in the data, that have not been resolved or deferred, and all error conditions that currently exist in the data. When called with a query that selects specific ISOK_QUERIES to execute, only the unresolved, undeferred, warnings and errors discovered by the executed ISOK_QUERIES are returned.

## Query Execution Order

When run_isok_queries() is called with no arguments, the queries are run in ISOK_QUERIES.IQName order, sorted lexically. When called with the text of an SQL query, the function runs the queries with the produced ISOK_QUERIES.IQNames, in the order given.

## 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[10] 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, undeferred warnings, 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 when supplying a a query as an argument to run_isok_queries, which thereby avoids 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')
ORDER BY 1$$) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;

Notice that the query used to select the queries to execute has an `ORDER BY` clause. Without such a clause, the order in which the queries are run, and so the ordering of the rows returned, is unspecified.

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'
ORDER BY isok_queries.iqname$$
) AS problems
ORDER BY problems.iqname
, problems.first_seen DESC
, problems.qr_id;

[10] 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 Functions (Running Isok) | Home | A. Security Considerations

Page generated: 2025-11-03T13:22:31-06:00.