SQL: повторно использовать значение, возвращаемое функцией?

  1. Как мне повторно использовать значение, возвращаемое pair, вызываемым в функции ниже?

    CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
    INSERT INTO chats SELECT pair($1, $2), $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair($1, $2));
    INSERT INTO messages VALUES (pair($1, $2), $1, $3);
$$ LANGUAGE SQL;

    Я знаю, что язык запросов SQL не поддерживает сохранение простых значений в переменных. как и обычный язык программирования. Итак, я просмотрел WITH Queries (Common Table Expressions). , но я не уверен, следует ли мне использовать WITH, и в любом случае я не мог понять правильный синтаксис того, что я делаю.

  2. Вот мой SQLFiddle и мой первоначальный вопрос о сохранение чатов и сообщений в PostgreSQL. Эта функция inserts-if-not-exists затем вставляет. Я не использую транзакцию, потому что хочу, чтобы все было быстро, и хранить чат без сообщений не так уж плохо, но хуже наоборот. Итак, порядок запросов имеет значение. Если есть лучший способ сделать что-то, пожалуйста, посоветуйте.

  3. Я хочу повторно использовать значение в основном для ускорения кода. Но оптимизирует ли интерпретатор SQL вышеописанную функцию автоматически? Тем не менее, я хочу писать хороший, СУХОЙ код.


postgresql sql variables sql-insert sql-function
person ma11hew28    schedule 14.03.2014    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Поскольку тело функции является процедурным, используйте язык plpgsql, а не SQL. :

CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
BEGIN
    INSERT INTO chats
        SELECT pair($1, $2), $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair($1, $2));
    INSERT INTO messages VALUES (pair($1, $2), $1, $3);
END
$$ LANGUAGE plpgsql;

Кроме того, если результат для повторного использования равен pair($1,$2), вы можете сохранить его в переменной:

CREATE FUNCTION messages_add(bigint, bigint, text) RETURNS void AS $$
DECLARE
    pair bigint := pair($1, $2);
BEGIN
    INSERT INTO chats
        SELECT pair, $1, $2
        WHERE NOT EXISTS (SELECT 1 FROM chats WHERE id = pair);
    INSERT INTO messages VALUES (pair, $1, $3);
END
$$ LANGUAGE plpgsql;
person Daniel Vérité    schedule 14.03.2014
comment
Почему тело функции, являющееся процедурным, заслуживает использования PL/pgSQL? - person ma11hew28; 15.03.2014
comment
@MattDiPasquale: PL означает процедурный язык, он обеспечивает то, чего не хватает SQL с точки зрения переменных и контроля выполнения. Преимущество SQL как функционального языка заключается в потенциальном встраивании одиночных SELECT, но в данном случае двух INSERT это неприменимо. - person Daniel Vérité; 17.03.2014

arrow_upward
1
arrow_downward 

create function messages_add(bigint, bigint, text) returns void as $$
    with p as (
        select pair($1, $2) as p
    ), i as (
        insert into chats
        select (select p from p), $1, $2
        where not exists (
            select 1
            from chats
            where id = (select p from p)
        )
    )
    insert into messages
    select (select p from p), $1, $3
    where exists (
        select 1
        from chats
        where id = (select p from p)
    )
    ;
$$ language sql;

Он будет вставляться в сообщения только в том случае, если он существует в чатах.

person Clodoaldo Neto    schedule 14.03.2014
comment
Спасибо, Клодоальдо! Но я хочу, чтобы он вставлялся в messages, даже если строка уже существует в chats, чтобы вы могли добавить несколько сообщений в один чат. Под чатом я подразумеваю беседу или тред. - person ma11hew28; 15.03.2014

arrow_upward
0
arrow_downward

  1. На самом деле у меня нет ответа на эту часть вашего вопроса, но я все равно отвечу на него, потому что Markdown ОТСТОЙ и Stack Overflow не поддерживает атрибут start для элемента ol.

  2. В ПОРЯДКЕ. Теперь, когда мы достигли 2, :-) Функции PostgreSQL являются транзакционными. Таким образом, порядок на самом деле не имеет значения, поскольку обе вставки будут зафиксированы вместе в одной транзакции.

  3. Нет, оптимизатор предварительно оценивает неизменяемые функции только тогда, когда они вызываются с постоянными (не переменными) аргументами, например, pair(4, 5). См. Категории волатильности функций.

person ma11hew28    schedule 15.03.2014