Руководство по использованию предложения WITH в SQL

Я понимаю, как использовать предложение WITH для рекурсивных запросов (!!), но у меня возникают проблемы с пониманием его общего использования/мощности.

Например, следующий запрос обновляет одну запись, идентификатор которой определяется с помощью подзапроса, возвращающего идентификатор первой записи по метке времени:

update global.prospect psp
set    status=status||'*'
where  psp.psp_id=(
           select  p2.psp_id
           from    global.prospect p2
           where   p2.status='new' or p2.status='reset'
           order   by p2.request_ts
           limit   1 )
returning psp.*;

Будет ли это хорошим кандидатом для использования обертки WITH вместо относительно уродливого подзапроса? Если да, то почему?

Ответ 1

Если во включенных таблицах может быть параллельный доступ для записи, в приведенных выше запросах есть условия гонки. Рассмотрим:

Postgres UPDATE... LIMIT 1

В вашем примере можно использовать CTE (общее табличное выражение), но это не даст вам ничего, что невозможно сделать в подзапросе:

WITH x AS (
   SELECT  psp_id
   FROM    global.prospect
   WHERE   status IN ('new', 'reset')
   ORDER   BY request_ts
   LIMIT   1
   )
UPDATE global.prospect psp
SET    status = status || '*'
FROM   x
WHERE  psp.psp_id = x.psp_id
RETURNING psp.*;

BTW, возвращаемая строка будет обновленной версией.

Если вы хотели вставить возвращенную строку в другую таблицу, то там, где предложение WITH становится существенным:

WITH x AS (
   SELECT  psp_id
   FROM    global.prospect
   WHERE   status IN ('new', 'reset')
   ORDER   BY request_ts
   LIMIT   1
   ), y AS (
   UPDATE global.prospect psp
   SET    status = status || '*'
   FROM   x
   WHERE  psp.psp_id = x.psp_id
   RETURNING psp.*
   )
INSERT INTO z
SELECT *
FROM   y

Изменение данных с помощью CTE возможно с помощью PostgreSQL 9.1 или более поздней версии.
Подробнее читайте в отличном руководстве.

Ответ 2

WITH позволяет определить "временные таблицы" для использования в запросе SELECT. Например, я недавно написал такой запрос, чтобы вычислить изменения между двумя наборами:

-- Let o be the set of old things, and n be the set of new things.
WITH o AS (SELECT * FROM things(OLD)),
     n AS (SELECT * FROM things(NEW))

-- Select both the set of things whose value changed,
-- and the set of things in the old set but not in the new set.
SELECT o.key, n.value
    FROM o
    LEFT JOIN n ON o.key = n.key
    WHERE o.value IS DISTINCT FROM n.value

UNION ALL

-- Select the set of things in the new set but not in the old set.
SELECT n.key, n.value
    FROM o
    RIGHT JOIN n ON o.key = n.key
    WHERE o.key IS NULL;

Определив "таблицы" o и n вверху, я смог избежать повторения выражений things(OLD) и things(NEW).

Конечно, мы могли бы устранить UNION ALL с помощью FULL JOIN, но я не смог этого сделать в моем конкретном случае.

Если я правильно понимаю ваш запрос, он делает следующее:

Найдите самую старую строку в global.prospect, статус которой "новый" или "reset".
Отметьте это, добавив звездочку к ее статусу
Верните строку (включая нашу настройку на status).

Я не думаю, что WITH упростит что-нибудь в вашем случае. Может быть несколько более элегантно использовать предложение FROM, хотя:

update global.prospect psp
set    status = status || '*'
from   ( select psp_id
         from   global.prospect
         where  status = 'new' or status = 'reset'
         order  by request_ts
         limit  1
       ) p2
where  psp.psp_id = p2.psp_id
returning psp.*;

<суб > непроверенная. Сообщите мне, если это работает.

Это в точности то, что у вас есть, кроме:

Это можно легко расширить, чтобы обновить несколько строк. В вашей версии, которая использует выражение подзапроса, запрос завершился неудачно, если подзапрос был изменен, чтобы получить несколько строк.
Я не использовал псевдоним global.prospect в подзапросе, поэтому его немного легче читать. Поскольку это использует предложение FROM, вы получите сообщение об ошибке, если вы случайно ссылаетесь на обновляемую таблицу.
В вашей версии выражение подзапроса встречается для каждого отдельного элемента. Хотя PostgreSQL должен оптимизировать это и оценивать только одно выражение, эта оптимизация исчезнет, если вы случайно ссылаетесь на столбец в psp или добавляете изменчивое выражение.