Так как описано в примере нет. with связан со своим select.
1. Если запросы мудреные (групповые и по смыслу не связанные), то заранее создать temp таблицу с данными из with и производить выборку оттуда.
2. Конкретно пример из вопроса делается за 1 select, используя предложение OVER https://msdn.microsoft.com/ru-ru/library/ms175997(...
Насчет Postgresql не скажу, но в Oracle я бы сделал в тригере before insert по таким шагам (можно на примере этого на постгресе попробовать):
1. select nvl( max( to_number( substr(id,length(prefix)+1) ) ), 0) into res from Institutions where prefix=:new.prefix;
Выбираем максимальное число в строке с заданным префиксом, вырезая из строки префикс и приводя остальное к числу. Если ничего не возвращает значит, автоматом ставим 0.
2. если =0, значит не было таких документов, иначе в res максимум. :new.id := :new.prefix||trim(to_char(res+1,'00000'));
в ID соединяем префикс и следующий номер, номер приводим к char по формату (чтобы были нули впереди).
Другой вопрос, если имелось ввиду, что ранее были префиксы ППР, затем пошел префикс ППК, и когда идет снова ППР он должен начинаться с 1. То тут надо будет смотреть в разрезе последних вводившихся (может с датой есть какое поле и отбирать предыдущий префикс оттуда).
