-- Procedure searchall() set the search_path to all schemas
-- of current database in alphabetical order.

-- Usage:    CALL searchall();

CREATE PROCEDURE searchall() LANGUAGE plpgsql AS $body$
DECLARE
  cmd text = 'set search_path to ';
  schema_name text;
BEGIN
  FOR schema_name IN 
    SELECT nspname
    FROM pg_catalog.pg_namespace
    WHERE nspname !~ '^pg_' AND nspname <> 'information_schema'
    ORDER BY 1
  LOOP
    cmd = cmd || schema_name || ',';
  END LOOP;
  cmd = trim (TRAILING FROM cmd, ',');
  EXECUTE cmd;
END;
$body$;
