Внутренние запросы PostgreSQL с подготовленными операторами

У меня есть таблица для хранения контактов.

Я хочу получить максимальное значение столбца, в котором добавление user_id {some number} и установка его в качестве того же значения столбца для текущей записи вставки.

Я использую подготовленные заявления:

pg_prepare($db, "add", 'INSERT INTO '.CONTACTS.' (c_user_serial,c_name,c_company,c_email) VALUES ($1, $2, $3, $4)'); $insert_co = pg_execute($db, "add", array({(MAX OF c_user_serial where c_user_id = 1234) + 1 increment },$name,$company,$email)); 

c_user_id – это идентификатор пользователя, который добавляет этот контакт, есть другой столбец в качестве индекса ( id ), который является общим serial столбцом, который увеличивается для каждой строки, c_user_serial – это порядковый номер, который увеличивается на каждого пользователя. Скажем, один пользователь добавил один контакт, чтобы он был 1 . После того, как другие пользователи добавили много контактов, когда этот пользователь добавляет свой второй контакт, я хочу, чтобы этот столбец сохранил 2 , поэтому автоматически увеличивайте тип столбца, но он должен увеличиваться на пользователя.

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

Этот запрос будет делать то, о чем вы просите:

 INSERT INTO contacts (c_user_serial, c_user_id, c_name, c_company, c_email) SELECT max(c_user_serial) + 1, $1, $2, $3, $4 FROM tbl WHERE c_user_id = $1; 

Ваш оригинал забывает вставлять сам user_id , что необходимо. Я добавил.

Однако это сопряжено с несколькими принципиальными проблемами:

  1. Текущий «максимум» может быть предметом условия гонки между параллельными транзакциями и ненадежным. (В то время как serial является безопасным с одновременным доступом.)

  2. Если ни одна строка с c_user = $1 не найдена, ничего не вставлено. Может или не может быть то, что вы хотите.

  3. Если max(c_user_serial) возвращает NULL, для c_user_id другое значение NULL.
    Если c_user_id определено NOT NULL, этого не может быть.

Чтобы избежать проблем 2. и 3. и начинайте с 1 для каждого нового пользователя:

 INSERT INTO contacts (c_user_serial, c_user_id, c_name, c_company, c_email) VALUES (COALESCE((SELECT max(c_user_serial) + 1 FROM tbl WHERE c_user_id = $1), 1) , $1, $2, $3, $4) 

«никакая строка» не конвертируется в NULL, а COALESCE умолчанию – 1 в этом случае.

Простое решение

Все вместе, простое решение :

 pg_prepare($db, "add" , 'INSERT INTO ' . CONTACTS . ' (c_user_serial, c_user_id, c_name, c_company, c_email) VALUES (COALESCE((SELECT max(c_user_serial) + 1 FROM tbl WHERE c_user_id = $1), 1) , $1, $2, $3, $4)'); $insert_co = pg_execute($db, "add", array($user_id,$name,$company,$email)); 

Убедитесь, что CONTACTS содержит правильно экранированное имя таблицы или вы широко открыты для SQL-инъекций !

Если вы ищете последовательности без пробелов , вы должны иметь дело с UPDATE и DELETE каким-то образом , что неизбежно сотнет ваши последовательности с течением времени, что является одной из причин, почему вся идея не так хороша .

Другая, более важная причина – состояние гонки, о котором я упоминал. Для этого нет дешевого и элегантного решения . (Есть решения, но более или менее дорогие …)

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

  • Серийные номера на группу строк для составного ключа