The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.
melkij=> VALUES ('кофты'), ('ботинки'), ('пальто');
column1
---------
кофты
ботинки
пальто
(3 строки)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);CREATE TRIGGER tr_users_bi
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
DECLARE allowed_created_at DATETIME;
DECLARE error_message_text VARCHAR(64);
SELECT MAX(created_at) + INTERVAL 12 HOUR INTO allowed_created_at
FROM users
WHERE username = NEW.username;
IF NEW.created_at < allowed_created_at THEN
SET error_message_text = CONCAT('Регистрация возможна не ранее ', allowed_created_at);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_message_text;
END IF;
END
Если кто-то набросает какой-нибудь PL/pgSQL код в любом фиддле, для иллюстрации что на нем можно делать - буду премного благодарен.
