-- Test that the system reports a sensible error when
-- the executed sql raises a sql_routine_exception

BEGIN;

-- run_isok_queries() expects an expression that returns results
-- from a table.  So create a function that does that, but instead
-- raises the error we want to test.

CREATE FUNCTION test_func()
  RETURNS TABLE (c1 TEXT, c2 TEXT, c3 TEXT)
  LANGUAGE plpgsql
  AS $$
     BEGIN
       RAISE EXCEPTION sql_routine_exception USING
         MESSAGE = 'Test of sql_routine_exception';
     END;
     $$;

-- Update the query to raise the error
UPDATE isok_queries
  SET query = $$SELECT * FROM test_func();$$
  WHERE iqname = 'query1';

-- Run to see trapped error
SELECT irid, iqname, qr_id, qr_message, qr_extra
  FROM run_isok_queries($$VALUES ('query1');$$)
  ORDER BY irid;

ROLLBACK;
