-- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc., -- http://www.karlpinc.com/ -- Copyright (C) 2004-2011, Karl O. Pinc -- -- 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 . -- -- Karl O. Pinc -- 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 , category @extschema@.isok_results.category%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: -- -- 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 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 , category @extschema@.isok_results.category%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_caught BOOLEAN := FALSE; BEGIN -- Run the supplied query and iterate over the saved queries. FOR this_iqname IN EXECUTE query 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 (' || textualize(this_iqname) || ') 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.category , @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; RAISE EXCEPTION data_exception USING MESSAGE = 'run_isok_queries:' || ' Error executing supplied query argument' , DETAIL = E'\nThe error is: ' || my_message_text || my_pg_exception_detail || my_pg_exception_hint || E'\nThe query is:\n' || query || my_pg_exception_context; END IF; -- WHEN OTHERS THEN -- RAISE EXCEPTION data_exception USING -- MESSAGE = 'SQLSTATE ' || SQLSTATE || ': SQLERRM ' || SQLERRM; END; $$;