run_integrity_queries — execute one or more of the queries stored in the INTEGRITY_QUERIES table
TABLE (iwid, iqname, first_seen, last_seen, category,
warning_id, warning_message, notes)
run_integrity_queries
(
|
); |
TABLE (iwid, iqname, first_seen, last_seen, category,
warning_id, warning_message, notes)
run_integrity_queries
(
|
iq_query); |
TEXT
iq_query
;The text of an SQL query. The query must return a single column of INTEGRITY_QUERIES.IQName values.
A function which runs the queries stored in the
INTEGRITY_QUERIES table, returns the output
of the stored queries, and stores the results in the
INTEGRITY_WARNINGS 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 INTEGRITY_QUERIES are run. When called with the text of an SQL query, a query which returns a single column containing INTEGRITY_QUERIES.IQName values, the function runs only those queries.
Use PostgreSQL's dollar
quoting when supplying a
query to run_integrity_queries().
The function returns a set of columns with multiple
rows, a table. So it must be used in the FROM
clause of a SELECT statement. The columns
returned by the function are the columns of the
INTEGRITY_WARNINGS table, excepting the
Resolved column and the
Deferred_To
column.
The rows returned by the function are those of the newly
updated INTEGRITY_WARNINGS 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
INTEGRITY_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
INTEGRITY_QUERIES to execute, only the
unresolved warnings and errors discovered by the executed
INTEGRITY_QUERIES are returned.
Running an INTEGRITY_QUERIES.Query does more than add new rows to the INTEGRITY_WARNINGS table. The INTEGRITY_QUERIES.Last_Run column is updated with a new timestamp as is the INTEGRITY_WARNINGS.Last_Seen value of all INTEGRITY_WARNINGS rows with IQName values matching that of the executed query where the Warning_Id value matches the value returned in the first column of the executed query.
Further, if an existing INTEGRITY_WARNINGS row matches the IQName value of the executed query but there is no corresponding Warning_Id value returned by the executed query then the INTEGRITY_WARNINGS row is deleted. This empties the INTEGRITY_WARNINGS table of errors and warnings that no longer apply to the current state of the database. This happens to warnings regardless of whether or not the warning is resolved.
If significant research has gone into the resolution of a warning condition that is expected, for whatever reason, to be absent from the database and then re-appear then care should be taken to record this research somewhere other than in the INTEGRITY_WARNINGS table. The row corresponding to the warning condition in INTEGRITY_WARNINGS may be automatically deleted by the warning sub-system when the warning condition is temporarily absent from the database content.
The following example runs all the queries in INTEGRITY_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 H.2. Executing all INTEGRITY_QUERIES
SELECT *
FROM run_integrity_queries() AS problems
ORDER BY problems.iqname
, problems.first_seen desc
, problems.warning_id;
The following example runs a single saved query with an
INTEGRITY_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_integrity_queries and thereby
avoid problems having to do with trying to nest regular
quotes.
Example H.3. Executing a single INTEGRITY_QUERIES.Query
SELECT *
FROM run_integrity_queries($$SELECT 'mycheck'$$) AS problems
ORDER BY problems.iqname
, problems.first_seen desc
, problems.warning_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.
This example also demonstrates how to use dollar
quoting to
give a query to run_integrity_queries and thereby
avoid problems having to do with trying to nest regular
quotes.
Example H.4. Executing INTEGRITY_QUERIES of the “bdate” type
SELECT *
FROM run_integrity_queries(
$$SELECT integrity_queries.iqname
FROM integrity_queries
WHERE integrity_queries.type = 'bdate'$$
) AS problems
ORDER BY problems.iqname
, problems.first_seen desc
, problems.warning_id;