Name

run_integrity_queries — execute one or more of the queries stored in the INTEGRITY_QUERIES table

Synopsis

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 ;
 

Input

iq_query

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

Description

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.

Tip

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.

Caution

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.

Examples

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;

          



Page generated: 2024-05-31T10:38:21-04:00.