Функция PLPGSQL, возвращающая триггер И значение

Я написал функцию PL/PGSQL, которая возвращает триггер, поэтому я могу вызывать ее перед вставкой каждой строки. Теперь я понимаю, что мне также хотелось бы, чтобы эта функция возвращала идентификатор вновь вставленной строки. Я не совсем уверен, как действовать, поскольку моя функция должна возвращать триггер. Вот код:

CREATE OR REPLACE FUNCTION f_insert_album() RETURNS TRIGGER AS $$
DECLARE
    subj_album_id INTEGER;
BEGIN
    -- ... some parts where left out
    INSERT INTO t_albums_subjective (user_id, album_id, format_id, location_id, rating) 
    VALUES (NEW.user_id, obj_album_id, NEW.format_id, NEW.location_id, NEW.rating) 
    RETURNING id INTO subj_album_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Bind insert function to trigger
DROP TRIGGER IF EXISTS tr_v_albums_insert ON v_albums; 
CREATE TRIGGER tr_v_albums_insert INSTEAD OF INSERT ON v_albums
    FOR EACH ROW EXECUTE PROCEDURE f_insert_album();

Я должен сохранить возвращаемый тип моей функции от f_insert_album() до TRIGGER, но мне бы очень хотелось также вернуть значение в subj_album_id, соответствующее id вновь вставленной строки.

Есть какой-либо способ сделать это? Это вообще возможно? Очевидно, что изменение типа возвращаемого значения не работает с Postgres. Не могли бы вы предложить альтернативный подход, если таковой имеется?


postgresql triggers sql-view plpgsql sql-returning
person John Pink    schedule 02.11.2015    source источник
comment
Вам нужно будет сохранить сгенерированный идентификатор в кортеже, который запустил триггер, если есть соответствующее поле, и использовать INSERT ... RETURNING... также для запроса верхнего уровня. Или используйте полезную нагрузку уведомления через LISTEN и NOTIFY.   -  person Craig Ringer    schedule 02.11.2015
comment
Спасибо, Крейг, хотя я не совсем уверен, что хорошо понимаю! Не могли бы вы уточнить вашу первую идею?   -  person John Pink    schedule 02.11.2015

Ответы (1)


arrow_upward
1
arrow_downward

Важнейший вопрос: куда вернуть идентификатор?

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

...
RETURNING id INTO subj_album_id;

Вместо этого назначьте его столбцу строки, запускающей триггер. Эта строка хранится в специальной переменной NEW. в триггерной функции:

...
RETURNING id INTO NEW.album_id;  -- use actual column name in view

Затем используйте предложение RETURNING оператора INSERT:

INSERT INTO v_albums (user_id, format_id, location_id, rating) 
VALUES ( ... ) 
RETURNING album_id;

Очевидно, что это возможно только в том случае, если в представлении есть видимый столбец. Однако его не обязательно назначать в команде INSERT. Тип переменной строки NEW определяется определением представления, а не имеющимся INSERT.

Тесно связанные:

Для того, что вы похоже делаете (предоставляете доступ к определенным строкам таблицы определенной роли), безопасность на уровне строк (RLS) в Postgres 9.5 или более поздних версиях может быть более удобная альтернатива:

person Erwin Brandstetter    schedule 02.11.2015
comment
Миллиард спасибо! Это сработало, и тесно связанный вопрос очень помог. - person John Pink; 06.11.2015